[Firebird-devel] [FB-Tracker] Created: (CORE-6343) Rolled back transaction produces unexpected results leading to duplicate values in PRIMARY KEY field

[Tomasz J (JIRA)]

Rolled back transaction produces unexpected results leading to duplicate values 
in PRIMARY KEY field
----------------------------------------------------------------------------------------------------

                 Key: CORE-6343
                 URL: http://tracker.firebirdsql.org/browse/CORE-6343
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
    Affects Versions: 3.0.5
         Environment: Linux, Windows, Classic Server
            Reporter: Tomasz J
            Priority: Critical


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).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel