Bug #66663 DELETE has wrong scope in SUBSELECT
Submitted: 3 Sep 2012 10:03 Modified: 3 Sep 2012 13:12
Reporter: Christian Becker Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.27 OS:Linux
Assigned to: CPU Architecture:Any

[3 Sep 2012 10:03] Christian Becker
Description:
I tried to delete serveral rows from a Table based on Informations found in another Table. Due to a typo, i acidentally deleted everything in that Table due to a wrong scope of variables in subselects.

How to repeat:
Create two Tables for testing:
create table a (a int);                       
create table b (b int);

Insert two rows in Table a
insert into a (a) values (1), (2);

Insert one row in Table b
insert into b (b) values (1);

Delete all rows from Table a which are found in Table b.
delete from a where a in (select a from b);
Query OK, 2 rows affected (0.00 sec) -> not supposed to happen.

Suggested fix:
Since it is possible to "join" using Subselects, there should occour the error:

ERROR 1054 (42S22): Unknown column 'a' in 'field list'

Although it should be possible to access the Field a using the Table name.

To Summarize:

delete from a where a in (select a from b);
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

delete from a where a in (select a.a from b);
Query OK, 1 rows affected (0.00 sec)
[3 Sep 2012 13:12] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

For explanation see bug #64726 and EXPLAIN output:

mysql>  explain extended select * from a where a in (select a from b);
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | a     | ALL    | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 |             |
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

Note (Code 1276): Field or reference 'test.a.a' of SELECT #2 was resolved in SELECT #1
Note (Code 1003): select `test`.`a`.`a` AS `a` from `test`.`a` where <in_optimizer>(`test`.`a`.`a`,<exists>(select 1 from `test`.`b` where (<cache>(`test`.`a`.`a`) = `test`.`a`.`a`)))
mysql> select * from a where a in (select a from b);
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
[19 Apr 2016 22:19] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=81146 marked as duplicate of this one.