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: | |
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
[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.