Skip to content
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

Closed
firebird-automations opened this issue Jan 14, 2010 · 12 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

reporter: Pavel Cisar [ pcisar ] => Karol Bieniaszewski [ livius ]

security: Managers [ 10013 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Critical [ 2 ] => Major [ 3 ]

Version: 2.5 RC1 [ 10362 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: 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

=>

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: 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

=>

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

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Vlad, I suppose this is one more example for the well-known issue you're testing the fix for :-)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I read once again what you wrote
as i understand correctly that first statement also should delete all rows? not only one?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Yes, rows to be deleted are marked according to this query:
SELECT FROM TEST T WHERE EXISTS(SELECT * FROM TEST T2 WHERE http://T2.ID<>http://T.ID AND T2.KOD=T.KOD)

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue relate to CORE3362 [ CORE3362 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Closed [ 6 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants