You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Recently we have noticed duplicate entries in primary key field. How to reproduce the issue:
Database:
CREATE GLOBAL TEMPORARY TABLE GTT_TABLE (
ID INTEGER NOT NULL
) ON COMMIT DELETE ROWS;
CREATE TABLE TEST_TABLE (
ID INTEGER NOT NULL PRIMARY KEY
);
CREATE OR ALTER PROCEDURE TEST_PROC
RETURNS (
ID1 INTEGER)
AS
DECLARE VARIABLE ID2 INTEGER;
BEGIN
INSERT INTO GTT_TABLE VALUES(1);
INSERT INTO GTT_TABLE VALUES(2);
INSERT INTO GTT_TABLE VALUES(3);
FOR SELECT ID FROM GTT_TABLE
INTO :ID1 DO
BEGIN
INSERT INTO TEST_TABLE (ID) VALUES (:ID1);
FOR SELECT 1 FROM RDB$DATABASE INTO :ID2 DO
IF \(:ID1=3\) THEN ID1 = 1/0; \-\-in production there is EXCEPTION EX\_NAME instead
SUSPEND;
DELETE FROM TEST\_TABLE;
END
END
Now using ISQL (reproduced also using other tools) execute:
1)
SQL> select * from test_proc;
ID1
============
1
2
Statement failed, SQLSTATE = 22012
arithmetic exception, numeric overflow, or string truncation
-Integer divide by zero. The code attempted to divide an integer value by an integer divisor of zero.
-At procedure 'TEST_PROC' line: 16, col: 38
Result as expected.
2)
SQL> ROLLBACK;
3)
SQL> SELECT * FROM TEST_TABLE;
ID
============
2
3
Expected result: empty dataset.
Now repeat steps 1), 2), 3) effects of 1) and 2) are the same ( 1) should already raise primary key violation) , but the final result is:
SQL> SELECT * FROM TEST_TABLE;
ID
============
2
3
2
3
I have reproduced the above in 3.0.5 Linux, 3.0.4 Windows, 3.0.3 Linux (all Classic).
I hope I have included enough details (this is my first ticket).
Submitted by: Tomasz J (tommy)
Recently we have noticed duplicate entries in primary key field. How to reproduce the issue:
Database:
CREATE GLOBAL TEMPORARY TABLE GTT_TABLE (
ID INTEGER NOT NULL
) ON COMMIT DELETE ROWS;
CREATE TABLE TEST_TABLE (
ID INTEGER NOT NULL PRIMARY KEY
);
CREATE OR ALTER PROCEDURE TEST_PROC
RETURNS (
ID1 INTEGER)
AS
DECLARE VARIABLE ID2 INTEGER;
BEGIN
INSERT INTO GTT_TABLE VALUES(1);
INSERT INTO GTT_TABLE VALUES(2);
INSERT INTO GTT_TABLE VALUES(3);
FOR SELECT ID FROM GTT_TABLE
INTO :ID1 DO
BEGIN
INSERT INTO TEST_TABLE (ID) VALUES (:ID1);
END
Now using ISQL (reproduced also using other tools) execute:
1)
SQL> select * from test_proc;
============
1
2
Statement failed, SQLSTATE = 22012
arithmetic exception, numeric overflow, or string truncation
-Integer divide by zero. The code attempted to divide an integer value by an integer divisor of zero.
-At procedure 'TEST_PROC' line: 16, col: 38
Result as expected.
2)
SQL> ROLLBACK;
3)
SQL> SELECT * FROM TEST_TABLE;
============
2
3
Expected result: empty dataset.
Now repeat steps 1), 2), 3) effects of 1) and 2) are the same ( 1) should already raise primary key violation) , but the final result is:
SQL> SELECT * FROM TEST_TABLE;
============
2
3
2
3
I have reproduced the above in 3.0.5 Linux, 3.0.4 Windows, 3.0.3 Linux (all Classic).
I hope I have included enough details (this is my first ticket).
Commits: 8b328f8
The text was updated successfully, but these errors were encountered: