Bug #94858 Deletion count incorrect when rows deleted through multi-hop foreign keys
Submitted: 2 Apr 1:35 Modified: 2 Apr 13:34
Reporter: Sawyer Knoblich Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.15 OS:Any
Assigned to: CPU Architecture:Any

[2 Apr 1:35] Sawyer Knoblich
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
[2 Apr 13:34] Sinisa Milivojevic
Hi,

Thank you for your bug report.

I was indeed capable of repeating the same behaviour with 8.0.15.

Verified as reported.