MySQL Bugs: #29905: Explain reporting incorrect select

Bug #29905	Explain reporting incorrect select_type for subquery
Submitted:	19 Jul 2007 17:08	Modified:	20 Jul 2007 6:11
Reporter:	Boyd Hemphill	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (FC6)
Assigned to:		CPU Architecture:	Any

Description:
Explain is reporting a dependent subquery when "in" is used for comparison rather than "="

How to repeat:
select_type is reported as a SUBQUERY which is correct.

mysql> explain select name from City where name  = (select name from City where name='new york');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | City  | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where | 
|  2 | SUBQUERY    | City  | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.04 sec)

Below the comparison operator is changed to "in" and the report changes.

mysql> explain select name from City where name  in (select name from City where name='new york');
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | City  | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where | 
|  2 | DEPENDENT SUBQUERY | City  | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where | 
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.03 sec)

I thought maybe the optimizer was confused so I helped by specifying aliases but it still reports a dependency.  

mysql> explain select name from City c1 where c1.name  in (select c2.name from c2.City where c2.name='new york');
ERROR 1146 (42S02): Table 'c2.City' doesn't exist
mysql> explain select name from City c1 where c1.name  in (select c2.name from City c2 where c2.name='new york');
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | c1    | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where | 
|  2 | DEPENDENT SUBQUERY | c2    | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where | 
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Suggested fix:
It is hard to know if this is just EXPLAIN reporting something wrong or the optimizer functioning incorrectly.

In either case, the server should recognize these as SUBQUERY type because no dependency exists.

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

Query with IN clause is expanding to DEPENDENT SUBQUERY:

$mysql50
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1232
Server version: 5.0.48-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.16 sec)

mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.00 sec)

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

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

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

mysql> explain extended select f1 from t1 where f1 = (select f1 from t2 where f1 = 1);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where | 
|  2 | SUBQUERY    | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (`test`.`t1`.`f1` = (select `test`.`t2`.`f1` AS `f1` from `test`.`t2` where (`test`.`t2`.`f1` = 1))) | 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

mysql> explain extended select f1 from t1 where f1 in (select f1 from t2 where f1 = 1);
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where | 
|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where | 
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                      |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`f1`,<exists>(select 1 AS `Not_used` from `test`.`t2` where ((`test`.`t2`.`f1` = 1) and (<cache>(`test`.`t1`.`f1`) = 1)))) | 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>