Bug #94858 Deletion count incorrect when rows deleted through multi-hop foreign keys

Submitted: 2 Apr 2019 1:35 Modified: 2 Apr 2019 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 2019 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 2019 13:34] MySQL Verification Team
Hi,

Thank you for your bug report.

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

Verified as reported.

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.