Bug #49200 Select_type in explain output incorrect for semijoin queries
Submitted: 30 Nov 2009 10:19 Modified: 30 Nov 2009 10:30
Reporter: Roy Lyseng Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to:
Tags: optimizer_switch, semijoin, subquery
Triage: Triaged: D3 (Medium)

[30 Nov 2009 10:19] Roy Lyseng
Description:
Select_type is actually assigned before first optimization of a SELECT query.
If the query on hand contains a subquery and this subquery is optimized to a
semijoin operation, so that the query is flatted into a single join structure,
then the Select_type is reported as PRIMARY instead of SIMPLE. For subsequent
executions, the Select_type is SIMPLE.

How to repeat:
create table t1(f1 integer);
insert into t1 values(1),(11);
prepare stmt from "explain select * from t1 where f1 in(select f1 from t1)";
execute stmt;
execute stmt;
deallocate prepare stmt;
drop table t1;
[30 Nov 2009 10:30] Valerii Kravchuk
Verified just as described with recent 6.0.14 from bzr on Linux:

openxs@suse:/home2/openxs/dbs/6.0-code> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> prepare stmt from "explain select * from t1 where f1 in(select f1 from t1)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |                             |
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; FirstMatch(t1) |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
2 rows in set (0.00 sec)

mysql> execute stmt;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |                             |
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; FirstMatch(t1) |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
2 rows in set (0.00 sec)