Bug #22514 Delete with invalid subquery deletes instead of returning error
Submitted: 20 Sep 2006 12:32 Modified: 20 Sep 2006 13:15
Reporter: Jonathon Coombes Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.24,5.1.7 OS:Linux (Linux,Mac OS X)
Assigned to: CPU Architecture:Any

[20 Sep 2006 12:32] Jonathon Coombes
Description:
Wanted to delete a certain set of records from a table using subselect. Query is:

delete from table1 where customer_id in (select customer_id from table2 where condition)

On executing this statement, MySQL deleted all records in table1. 

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

mysql> select count(*) from customer_alerts;
+----------+
| count(*) |
+----------+
|     3636 |
+----------+
1 row in set (0.01 sec)

mysql> delete from customer_alerts where customer_id in (select customer_id from customer where email='nobody@fakeaddress.com.au' and username like 'lr%'); 
Query OK, 3636 rows affected (1.15 sec)

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

mysql> select customer_id from customer where email='nobody@fakeaddress.com.au' and username like 'lr%';
ERROR 1054 (42S22): Unknown column 'customer_id' in 'field list'

On examining the subquery, it turns out that customer_id does not exist within table2 and returned an appropriate error. However, this error in the subselect seems to be ignored and continues the delete matching all records in table1?

Results initially in a 5.0.18 version on Linux, but tried to reproduce on 5.1.7 on Mac OS X and got the same result - all deleted.

How to repeat:
Create two tables and populate.

Construct the query in the format shown above where the second table does not have the column being queried in the subselect.

Suggested fix:
Should throw an error if the subselect returns an error.
[20 Sep 2006 12:40] Mark Leith
Thank you for the bug report, verified as stated:

+---------------------+
| version()           |
+---------------------+
| 5.0.24-standard-log | 
+---------------------+
1 row in set (0.91 sec)

mysql> use test
Database changed
mysql> create table t1 (i int, j int);
Query OK, 0 rows affected (0.23 sec)

mysql> create table t2 (i int);
Query OK, 0 rows affected (0.05 sec)

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

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

mysql> select * from t1;
+------+------+
| i    | j    |
+------+------+
|    1 |    1 | 
|    2 |    2 | 
|    3 |    3 | 
+------+------+
3 rows in set (0.11 sec)

mysql> select * from t2;
+------+
| i    |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.01 sec)

mysql> delete from t1 where i in (select j from t2 where i = 1);
Query OK, 3 rows affected (0.26 sec)

mysql> select * from t1;
Empty set (0.01 sec)

mysql> select j from t2 where i = 1;
ERROR 1054 (42S22): Unknown column 'j' in 'field list'
[20 Sep 2006 12:47] Domas Mituzas
j exists in outer query, and as it is equal to 'i'.

Same is with customer_id - outer query value is used in subquery, as internal value did not exist
[26 Sep 2006 17:52] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=22706 was marked as duplicate of
this one.