MySQL Bugs: #12106: EXPLAIN shows incorrect select

Bug #12106	EXPLAIN shows incorrect select_type for subquery
Submitted:	22 Jul 2005 8:40	Modified:	29 Mar 2006 13:38
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.12a-nt, 5.0.9-beta-nt	OS:	Windows (XP)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
According to the manual (http://dev.mysql.com/doc/mysql/en/explain.html), select types for subquery shown by EXPLAIN are defined as follows:

"- select_type

      The type of SELECT, which can be any of the following:
            ...
            - SUBQUERY
            First SELECT in subquery
            - DEPENDENT SUBQUERY
            First SELECT in subquery, dependent on outer query
            ...
"

But there is a simple case, when DEPENDENT SUBQUERY is shown for the subquery that is absolutely independent. It should be SUBQUERY simply, according to the manual.

How to repeat:
In any database execute:

create table t1 (c integer);
create table t2 (c integer);

mysql> select * from t1 where c in (select t2.c from t2);
Empty set (0.01 sec)

mysql> explain select * from t1 where c in (select t2.c from t2);
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref
 | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL
 |    0 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL
 |    0 | Using where |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+-------------+
2 rows in set (0.00 sec)

Suggested fix:
The only place in sql/sql_select.cpp where "DEPENDENT SUBQUERY" mentioned is near line 13652 (mysql_explain_union()). The decision is somehow related to the uncachable local variable.

It may be not a bug in the code itself, but then we need to explain in the manual all the possible cases, when DEPENDENT SUBQUERY is shown.

I think the problem is a bit deeper than just the label. If you add primary keys to Valeriy's test case:

create table t1 (c integer primary key);
create table t2 (c integer primary key);

note that the explain shows an index scan on the outer table. This makes the performance of the query far worse than the key look-up it should be using.

+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type        | table | type            | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY            | t1    | index           | NULL          | PRIMARY |       4 | NULL |    0 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t2    | unique_subquery | PRIMARY       | PRIMARY |       4 | func |    1 | Using index              |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+

Are there any updates on this bug?
The issue is still present in 5.0.18 and it severely degrades the performance of these queries.

It's not a bug, this is how such subqueries are implemented now.
Use 'explain extended...', then 'show warnings' to see what's happening.

We have a corresponding task in the our todo list.