Bug #94858 Deletion count incorrect when rows deleted through multi-hop foreign keys
Description:
If you have a table which has a cascade-deleting foreign key through another table, and then back to itself, the count returned when running a delete query will not match the count that was actually deleted.
Even if you run a "delete all" on this table, if any of the elements are deleted from this table that would also have been otherwise deleted through this multi-hop cascade delete, the deletion count will not include that row.
Note that this works fine when the FK points directly at the same table, even if you point a row at another row which itself points at another row, etc. It seems to be specific to the FK jumping out to another table and then back.
How to repeat:
CREATE TABLE Entity
(
Id VARCHAR(1) PRIMARY KEY,
LinkId VARCHAR(1)
);
CREATE TABLE Link(
Id VARCHAR(1) PRIMARY KEY,
EntityId VARCHAR(1) NOT NULL
);
ALTER TABLE Link ADD FOREIGN KEY (EntityId) REFERENCES Entity (Id) ON DELETE CASCADE;
ALTER TABLE Entity ADD FOREIGN KEY (LinkId) REFERENCES Link(Id) ON DELETE CASCADE;
INSERT INTO Entity(Id)VALUES ('A');
INSERT INTO Link(Id, EntityId) VALUES ('X', 'A');
INSERT INTO Entity(Id, LinkId) VALUES ('B', 'X');
# The above 'B' row would be deleted even by just running `DELETE FROM Entity WHERE Id = 'A'`,
# since it has a cascade delete to Link 'X' which in turn has a cascade delete to Entity 'A'
SELECT * FROM Entity; # Returns 2 results
DELETE FROM Entity; # Returns "1 row affected" even though both Entity entries are deleted
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: If you have a table which has a cascade-deleting foreign key through another table, and then back to itself, the count returned when running a delete query will not match the count that was actually deleted. Even if you run a "delete all" on this table, if any of the elements are deleted from this table that would also have been otherwise deleted through this multi-hop cascade delete, the deletion count will not include that row. Note that this works fine when the FK points directly at the same table, even if you point a row at another row which itself points at another row, etc. It seems to be specific to the FK jumping out to another table and then back. How to repeat: CREATE TABLE Entity ( Id VARCHAR(1) PRIMARY KEY, LinkId VARCHAR(1) ); CREATE TABLE Link( Id VARCHAR(1) PRIMARY KEY, EntityId VARCHAR(1) NOT NULL ); ALTER TABLE Link ADD FOREIGN KEY (EntityId) REFERENCES Entity (Id) ON DELETE CASCADE; ALTER TABLE Entity ADD FOREIGN KEY (LinkId) REFERENCES Link(Id) ON DELETE CASCADE; INSERT INTO Entity(Id)VALUES ('A'); INSERT INTO Link(Id, EntityId) VALUES ('X', 'A'); INSERT INTO Entity(Id, LinkId) VALUES ('B', 'X'); # The above 'B' row would be deleted even by just running `DELETE FROM Entity WHERE Id = 'A'`, # since it has a cascade delete to Link 'X' which in turn has a cascade delete to Entity 'A' SELECT * FROM Entity; # Returns 2 results DELETE FROM Entity; # Returns "1 row affected" even though both Entity entries are deleted