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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 6.0
• OS: Any
• CPU Architecture: Any
• Tags: optimizer_switch, semijoin, subquery

[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] Valeriy 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)