Issue Details (XML | Word | Printable)

Key: CORE-6343
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Dmitry Yemanov
Reporter: Tomasz J
Votes: 0
Watchers: 8
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 24/Jun/20 03:31 PM   Updated: 26/Jun/20 06:35 AM
Component/s: Engine
Affects Version/s: 3.0.0, 3.0.1, 3.0.2, 3.0.3, 3.0.4, 3.0.5
Fix Version/s: 3.0.6

Environment: Linux, Windows, Classic Server

QA Status: Done successfully


 Description  « Hide
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).

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 24/Jun/20 04:28 PM
Confirmed on WI-V3.0.6.33322. No such problem on WI-V4.0.0.2073 (checked with ReadConsistency = 0 and 1) - table 'TEST_TABLE' remains empty..


Dmitry Yemanov made changes - 25/Jun/20 12:02 PM
Field Original Value New Value
Assignee Dmitry Yemanov [ dimitr ]
Dmitry Yemanov made changes - 25/Jun/20 05:07 PM
Fix Version/s 3.0.6 [ 10889 ]
Affects Version/s 3.0.4 [ 10863 ]
Affects Version/s 3.0.3 [ 10810 ]
Affects Version/s 3.0.2 [ 10785 ]
Affects Version/s 3.0.1 [ 10730 ]
Affects Version/s 3.0.0 [ 10740 ]
Dmitry Yemanov made changes - 25/Jun/20 05:08 PM
Status Open [ 1 ] Resolved [ 5 ]
Resolution Fixed [ 1 ]
Pavel Zotov made changes - 26/Jun/20 04:19 AM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status No test Done successfully
Pavel Zotov made changes - 26/Jun/20 04:19 AM
Status Resolved [ 5 ] Closed [ 6 ]
Dmitry Yemanov added a comment - 26/Jun/20 05:54 AM
Please test snapshot build 33326.

Pavel Zotov added a comment - 26/Jun/20 06:34 AM
Already tested. All OK.