MySQL Bugs: #44207: LEFT JOIN Multi-table DELETE fails to delete records when using foreign keys

Bug #44207	LEFT JOIN Multi-table DELETE fails to delete records when using foreign keys
Submitted:	10 Apr 2009 15:08	Modified:	10 Apr 2009 16:36
Reporter:	Sergey Petrunya	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1/5.0/6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
(Problem reported by Sergey Sytnik at Sun Tech Days)
LEFT JOIN multi-table DELETE fails to delete records when run over tables with certain PK/FK relationship.

How to repeat:
CREATE TABLE a (
	id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id),
	uid BIGINT
) ENGINE=InnoDB;

CREATE TABLE b (
	id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id),
	a_id BIGINT NOT NULL, CONSTRAINT fk_a__id FOREIGN KEY fk_idx_a__id (a_id) REFERENCES a (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE c (
	id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE d (
	id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id),
	b_id BIGINT NOT NULL, CONSTRAINT fk_b__id FOREIGN KEY fk_idx_b__id (b_id) REFERENCES b (id) ON DELETE CASCADE,
	c_id BIGINT NULL, CONSTRAINT fk_c__id FOREIGN KEY fk_idx_c__id (c_id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO a VALUES (1, 123), (2, 456), (3, 123);
INSERT INTO b VALUES (1, 1), (2, 2), (3, 3), (4, 3);
INSERT INTO c VALUES (1), (2), (3), (4), (5), (6);
INSERT INTO d VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 4, 5);

mysql> SELECT '=== BEFORE ===' AS 'dump'; SELECT *, 'a' AS name FROM a; SELECT *, 'b' AS name FROM b; SE
LECT *, 'c' AS name FROM c; SELECT *, 'd' AS name FROM d;
+----------------+
| dump           |
+----------------+
| === BEFORE === | 
+----------------+
1 row in set (0.00 sec)

+----+------+------+
| id | uid  | name |
+----+------+------+
|  1 |  123 | a    | 
|  2 |  456 | a    | 
|  3 |  123 | a    | 
+----+------+------+
3 rows in set (0.00 sec)

+----+------+------+
| id | a_id | name |
+----+------+------+
|  1 |    1 | b    | 
|  2 |    2 | b    | 
|  3 |    3 | b    | 
|  4 |    3 | b    | 
+----+------+------+
4 rows in set (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | c    | 
|  2 | c    | 
|  3 | c    | 
|  4 | c    | 
|  5 | c    | 
|  6 | c    | 
+----+------+
6 rows in set (0.00 sec)

+----+------+------+------+
| id | b_id | c_id | name |
+----+------+------+------+
|  1 |    1 |    1 | d    | 
|  2 |    2 |    2 | d    | 
|  3 |    3 |    3 | d    | 
|  4 |    4 |    4 | d    | 
|  5 |    4 |    5 | d    | 
+----+------+------+------+
5 rows in set (0.00 sec)

mysql> -- Run SELECT
mysql> 
mysql> SELECT a.*, c.*, '=== ? ===' AS 'test_select'
    -> FROM a
    -> LEFT JOIN b ON b.a_id = a.id
    -> LEFT JOIN d ON d.b_id = b.id
    -> LEFT JOIN c ON c.id = d.c_id
    -> WHERE a.uid = 123;
+----+------+------+-------------+
| id | uid  | id   | test_select |
+----+------+------+-------------+
|  1 |  123 |    1 | === ? ===   | 
|  3 |  123 |    3 | === ? ===   | 
|  3 |  123 |    4 | === ? ===   | 
|  3 |  123 |    5 | === ? ===   | 
+----+------+------+-------------+
4 rows in set (0.35 sec)

mysql> -- Run DELETE
mysql> 
mysql> DELETE a, c
    -> FROM a
    -> LEFT JOIN b ON b.a_id = a.id
    -> LEFT JOIN d ON d.b_id = b.id
    -> LEFT JOIN c ON c.id = d.c_id
    -> WHERE a.uid = 123;
Query OK, 4 rows affected (1.13 sec)

mysql> 
mysql> -- Show contents (after)
mysql> 
mysql> SELECT '=== AFTER ===' AS 'dump'; SELECT *, 'a' AS name FROM a; SELECT *,
 'b' AS name FROM b; SELECT *, 'c' AS name FROM c; SELECT *, 'd' AS name FROM d;
+---------------+
| dump          |
+---------------+
| === AFTER === | 
+---------------+
1 row in set (0.00 sec)

+----+------+------+
| id | uid  | name |
+----+------+------+
|  2 |  456 | a    |   (OK)
+----+------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | a_id | name |
+----+------+------+
|  2 |    2 | b    |  (OK)
+----+------+------+
1 row in set (0.00 sec)

+----+------+
| id | name |
+----+------+
|  2 | c    | 
|  4 | c    |  <-- this should have been deleted.
|  5 | c    |  <-- this should have been deleted.
|  6 | c    | 
+----+------+
4 rows in set (0.00 sec)

+----+------+------+------+
| id | b_id | c_id | name |
+----+------+------+------+
|  2 |    2 |    2 | d    |   (OK)
+----+------+------+------+
1 row in set (0.00 sec)

It is trying to delete records from the 1st table while running the join:

(gdb) wher
  #0  multi_delete::send_data (this=0x8f3cd60, values=@0x8ee68c0) at sql_delete.cc:712
  #1  0x0832e486 in end_send (join=0x8f44758, join_tab=0x8f46d60, end_of_records=false) at sql_select.cc:11912
  #2  0x08334e8f in evaluate_join_record (join=0x8f44758, join_tab=0x8f46bd8, error=0) at sql_select.cc:11172
  #3  0x08335084 in sub_select (join=0x8f44758, join_tab=0x8f46bd8, end_of_records=false) at sql_select.cc:11057
  #4  0x08334e8f in evaluate_join_record (join=0x8f44758, join_tab=0x8f46a50, error=0) at sql_select.cc:11172
  #5  0x08335084 in sub_select (join=0x8f44758, join_tab=0x8f46a50, end_of_records=false) at sql_select.cc:11057
  #6  0x08334e8f in evaluate_join_record (join=0x8f44758, join_tab=0x8f468c8, error=0) at sql_select.cc:11172
  #7  0x08335084 in sub_select (join=0x8f44758, join_tab=0x8f468c8, end_of_records=false) at sql_select.cc:11057
  #8  0x08334e8f in evaluate_join_record (join=0x8f44758, join_tab=0x8f46740, error=0) at sql_select.cc:11172
  #9  0x08335084 in sub_select (join=0x8f44758, join_tab=0x8f46740, end_of_records=false) at sql_select.cc:11057
  #10 0x0833d4c8 in do_select (join=0x8f44758, fields=0x8ee68c0, table=0x0, procedure=0x0) at sql_select.cc:10813
  #11 0x08356b45 in JOIN::exec (this=0x8f44758) at sql_select.cc:2182
  #12 0x0835199a in mysql_select (thd=0x8ee5490, rref_pointer_array=0x8ee6924, tables=0x8f3b538, wild_num=0, fields=@0x8ee68c0, conds=0x8f3cc08, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489942144, result=0x8f3cd60, unit=0x8ee65b4, select_lex=0x8ee682c) at sql_select.cc:2361
  #13 0x082cf146 in mysql_execute_command (thd=0x8ee5490) at sql_parse.cc:3242
...
(gdb) print delete_while_scanning
  $9 = true
(gdb) print secure_counter
  $10 = -1

Deletion in one table causes InnoDB to immediately delete records to satisfy PK-FK relationships, which then causes the join to miss records and not delete them.

If I use debugger to set delete_while_scanning=0 in multi_delete::initialize_tables(), then the DELETE statement works correctly.

The exact criteria of when it's possible to have delete_while_scanning=TRUE are quite complicated as they depend on the join order and whether the PK<->FK relationship(s) are one-to-one or one-to-many. 

First attempt:
"If a multi-table DELETE deletes from a table which is a (direct or indirect) PK/FK ancestor of some other table which is also used in the DELETE statement, set delete_while_scanning=FALSE".

Thank you for the bug report, verified as described. 5.0.51b and 5.1.26 older released versions present same behavior as well.