Issue Details (XML | Word | Printable)

Key: CORE-2799
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 0
Operations

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

Changing sort directon in delete statement cause deletion of all records in table

Created: 14/Jan/10 08:22 AM   Updated: 31/Jan/16 10:01 AM
Component/s: Engine
Affects Version/s: 2.1.3, 2.5 RC1
Fix Version/s: 3.0 Beta 1

Issue Links:
Relate
 

QA Status: Done successfully
Test Details:
Checked on:
     WI-T3.0.0.31374 Firebird 3.0 Beta 1
     WI-V3.0.0.32300 Firebird 3.0 Release Candidate 2
-- works OK.


 Description  « Hide
CREATE TABLE TEST
(
ID INTEGER NOT NULL PRIMARY KEY,
KOD VARCHAR(5)
);
COMMIT;
INSERT INTO TEST(ID, KOD) VALUES(1, 'abc');
INSERT INTO TEST(ID, KOD) VALUES(2, 'abc');
COMMIT;

now we have 2 rows in table
and delete in ascending oreder
DELETE FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE T2.ID<>T.ID AND T2.KOD=T.KOD) ORDER BY T.ID ASC
COMMIT;
one row affected ..

SELECT * FROM TEST;
one row selected ID=2 KOD='abc'

clear table
DELETE FROM TEST;
COMMIT;

once again insert the same records
INSERT INTO TEST(ID, KOD) VALUES(1, 'abc');
INSERT INTO TEST(ID, KOD) VALUES(2, 'abc');
COMMIT;

now we have 2 rows in table
and delete in descending oreder

DELETE FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE T2.ID<>T.ID AND T2.KOD=T.KOD) ORDER BY T.ID DESC
COMMIT;
two rows affected!

SELECT * FROM TEST;
empty result set!

you see that deleting with asc and desc do big difference but here should not
with asc sorting you delete 1 record which is ok
and with desc sorting you delete2 records(all) which is wrong


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 15/Jul/10 08:51 AM
Accordingly to the SQL standard, the second behavior (deletion of two rows) is correct, because the records must be marked for deletion prior to the deletion itself, i.e. the process should depend on the original record set.

Dmitry Yemanov added a comment - 14/Feb/11 11:32 AM
Vlad, I suppose this is one more example for the well-known issue you're testing the fix for :-)

Karol Bieniaszewski added a comment - 24/Feb/11 07:50 AM
I read once again what you wrote
as i understand correctly that first statement also should delete all rows? not only one?

Dmitry Yemanov added a comment - 24/Feb/11 08:01 AM
Yes, rows to be deleted are marked according to this query:
SELECT FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE T2.ID<>T.ID AND T2.KOD=T.KOD)