New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Changing sort directon in delete statement cause deletion of all records in table [CORE2799] #3189
Comments
Modified by: @pcisarreporter: Pavel Cisar [ pcisar ] => Karol Bieniaszewski [ livius ] security: Managers [ 10013 ] => |
Modified by: @livius2description: CREATE TABLE TEST now we have 2 rows in table SELECT * FROM TEST; clear table once again insert the same records now we have 2 rows in table DELETE FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE http://T2.ID<>http://T.ID AND T2.KOD=T.KOD) ORDER BY http://T.ID DESC SELECT * FROM TEST; you see that deleting with asc and desc do big difference but here should not => CREATE TABLE TEST now we have 2 rows in table SELECT * FROM TEST; clear table once again insert the same records now we have 2 rows in table DELETE FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE http://T2.ID<>http://T.ID AND T2.KOD=T.KOD) ORDER BY http://T.ID DESC SELECT * FROM TEST; you see that deleting with asc and desc do big difference but here should not |
Modified by: @livius2description: CREATE TABLE TEST now we have 2 rows in table SELECT * FROM TEST; clear table once again insert the same records now we have 2 rows in table DELETE FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE http://T2.ID<>http://T.ID AND T2.KOD=T.KOD) ORDER BY http://T.ID DESC SELECT * FROM TEST; you see that deleting with asc and desc do big difference but here should not => CREATE TABLE TEST now we have 2 rows in table SELECT * FROM TEST; clear table once again insert the same records now we have 2 rows in table DELETE FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE http://T2.ID<>http://T.ID AND T2.KOD=T.KOD) ORDER BY http://T.ID DESC SELECT * FROM TEST; you see that deleting with asc and desc do big difference but here should not summary: Bug with deletion - deleted rows count different for ASC and DESC sorting - but should not for this query => Changing sort directon in delete statement cause deletion of all records in table |
Commented by: @dyemanov 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. |
Commented by: @dyemanov Vlad, I suppose this is one more example for the well-known issue you're testing the fix for :-) |
Modified by: @dyemanovassignee: Vlad Khorsun [ hvlad ] |
Commented by: @livius2 I read once again what you wrote |
Commented by: @dyemanov Yes, rows to be deleted are marked according to this query: |
Modified by: @pavel-zotovstatus: Open [ 1 ] => Open [ 1 ] QA Status: Done successfully Test Details: Checked on: |
Modified by: @pavel-zotovstatus: Open [ 1 ] => Closed [ 6 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Beta 1 [ 10332 ] |
Submitted by: @livius2
Relate to CORE3362
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 http://T2.ID<>http://T.ID AND T2.KOD=T.KOD) ORDER BY http://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 http://T2.ID<>http://T.ID AND T2.KOD=T.KOD) ORDER BY http://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
====== 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.
The text was updated successfully, but these errors were encountered: