Description:
Not all records are deleted from the child table in a multi-table delete. I have reproduced this in both 7.1.3 and 7.1.13. Both INNER and LEFT JOINs produce this bug and the bug did NOT occur when the PRIMARY KEYs were removed from the table (UNIQUE KEY also produced the bug).
I found a bug that this seems similar to, however on 7.1.3 I couldn't reproduce it like mentioned: http://bugs.mysql.com/bug.php?id=61769
Another one that I found that seemed similar was http://bugs.mysql.com/bug.php?id=47054 however this also I could not reproduce in 7.1.3.
How to repeat:
mysql> DROP TABLE IF EXISTS a, b;
Query OK, 0 rows affected (0.93 sec)
mysql> CREATE TABLE a (a_id INT PRIMARY KEY);
Query OK, 0 rows affected (0.73 sec)
mysql> CREATE TABLE b (b_id INT PRIMARY KEY);
Query OK, 0 rows affected (0.64 sec)
mysql>
mysql> INSERT INTO a VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO b VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT *
-> FROM a
-> LEFT JOIN b ON a_id = b_id
-> WHERE a_id IN (1, 2, 3, 4, 5);
+------+------+
| a_id | b_id |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | NULL |
| 5 | NULL |
+------+------+
5 rows in set (0.01 sec)
mysql>
mysql> DELETE a, b
-> FROM a
-> INNER JOIN b ON a_id = b_id
-> WHERE a_id IN (1, 2, 6, 3);
Query OK, 4 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM a;
+------+
| a_id |
+------+
| 3 |
| 5 |
| 2 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM b;
Empty set (0.00 sec)
