Bug #41477 InnoDB foreign keys: wrong result if ON DELETE CASCADE, and correlated subquery

• Submitted: 15 Dec 2008 15:33
• Modified: 15 Dec 2008 17:11
• Reporter: Konstantin Osipov (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 4.1, 5.0, 5,1, 6.0 bzr
• OS: Any
• CPU Architecture: Any

[15 Dec 2008 15:33] Konstantin Osipov

Description:
If you delete records from the child table through a CASCADE, deletions happen row-by-row and a correlated subquery starts returning wrong results.
The set of rows to change, according to the standard, must be evaluated
before the change, not during the change.mysql> drop table if exists child, parent;
Query OK, 0 rows affected (0.05 sec)

mysql> set @@storage_engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> -- disable the effect of the optimizer
mysql> set @@optimizer_switch='no_materialization,no_semijoin';
Query OK, 0 rows affected (0.00 sec)

mysql> create table parent (pk int primary key, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table child (fk int,
    ->                    foreign key (fk) references parent (pk)
    ->                    on delete cascade, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into parent values (1,2),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into child values (1,2),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> -- should delete 2 rows, deletes only 1 row
mysql> delete from parent where
    ->  (select count(*) from child where b = parent.b) > 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from parent;
+----+------+
| pk | b    |
+----+------+
|  2 |    2 | 
+----+------+
1 row in set (0.01 sec)

How to repeat:
drop table if exists child, parent;
set @@storage_engine=innodb;
-- disable the effect of the optimizer
set @@optimizer_switch='no_materialization,no_semijoin';
create table parent (pk int primary key, b int);
create table child (fk int,
                   foreign key (fk) references parent (pk)
                   on delete cascade, b int);
insert into parent values (1,2),(2,2);
insert into child values (1,2),(2,2);
-- should delete 2 rows, deletes only 1 row
delete from parent where
 (select count(*) from child where b = parent.b) > 1;
select * from parent;

Suggested fix:
WL#148

[15 Dec 2008 17:11] Sveta Smirnova

Thank you for the report.

Verified as described.

[13 Feb 2012 8:30] Fayaz Yusuf Khan

I think my issue is also related to this.

mysql> create table A (id int primary key, parent_id int, constraint foreign key (parent_id) references A (id) on delete cascade) engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into A(id, parent_id) values (1,1),(2,1);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(*) from A;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.03 sec)

mysql> delete from A;
Query OK, 1 row affected (0.05 sec) <-- Says only one row affected.

mysql> select count(*) from A;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)