Bug #77191 Subquery on information_schema including an IN clause, does not return results
Submitted: 29 May 2015 9:14 Modified: 29 May 2015 10:15
Reporter: William Chiquito Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.21, 5.6.24, 5.7.8 OS:Any
Assigned to: CPU Architecture:Any
Tags: IN, information_schema, subquery

[29 May 2015 9:14] William Chiquito
Description:
A SELECT expression (to a table different from `information_schema`), with a subquery to `information_schema` using the IN clause, does not return results.

How to repeat:
mysql> SELECT VERSION();
+--------------+
| version()    |
+--------------+
| 5.6.21       |
+--------------+
1 row in set (0.00 sec)

mysql> USE `test`;

mysql> DROP TABLE IF EXISTS `tbl_test`;

mysql> CREATE TABLE `tbl_test` (
    -> `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> `name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
    -> );

mysql> INSERT INTO `tbl_test` (`name`)
    -> VALUES
    -> ('information_schema'),
    -> ('test');

    -> SELECT `id`, `name`
    -> FROM `test`.`tbl_test`
    -> WHERE `name` IN (
    ->        SELECT `SCHEMA_NAME`
    ->        FROM `information_schema`.`SCHEMATA`
    -> );
Empty set (0.01 sec)
[29 May 2015 10:15] Umesh Shastry
Hello William,

Thank you for the report.

Thanks,
Umesh
[29 May 2015 10:15] Umesh Shastry
// 5.5.45

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: bin/mysql -u root -p -S/tmp/mysql_ushastry.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.45-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> DROP TABLE IF EXISTS `tbl_test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE `tbl_test` (
    ->  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  `name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
    ->  );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `tbl_test` (`name`)
    ->  VALUES
    ->  ('information_schema'),
    ->  ('test');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql>  SELECT `id`, `name` FROM `test`.`tbl_test` WHERE `name` IN (SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`);
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | information_schema |
|  2 | test               |
+----+--------------------+
2 rows in set (0.00 sec)

mysql> explain  SELECT `id`, `name` FROM `test`.`tbl_test` WHERE `name` IN (SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`);
+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | tbl_test | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
|  2 | DEPENDENT SUBQUERY | SCHEMATA | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using where |
+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql>
[29 May 2015 10:16] Umesh Shastry
// 5.7.8, 5.6.24 - affected

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.8-rc-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS `tbl_test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE `tbl_test` (
    ->  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  `name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
    ->  );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `tbl_test` (`name`)
    ->  VALUES
    ->  ('information_schema'),
    ->  ('test');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT `id`, `name` FROM `test`.`tbl_test` WHERE `name` IN (SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`);
Empty set (0.00 sec)

mysql>
mysql> explain SELECT `id`, `name` FROM `test`.`tbl_test` WHERE `name` IN (SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`);
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                    |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------+
|  1 | SIMPLE      | tbl_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                                                     |
|  1 | SIMPLE      | SCHEMATA | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; FirstMatch(tbl_test); Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

// 5.6.24

[root@cluster-repo ~]# 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 51
Server version: 5.6.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>
mysql> DROP TABLE IF EXISTS `tbl_test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE `tbl_test` (
    ->  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  `name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
    ->  );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `tbl_test` (`name`)
    ->  VALUES
    ->  ('information_schema'),
    ->  ('test');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>  SELECT `id`, `name`
    ->  FROM `test`.`tbl_test`
    ->  WHERE `name` IN (
    ->         SELECT `SCHEMA_NAME`
    ->         FROM `information_schema`.`SCHEMATA`
    ->  );
Empty set (0.00 sec)
[29 May 2015 11:30] Øystein Grøvlen
Posted by developer:
 
Workaround:

mysql> set optimizer_switch='semijoin=off';                                     Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT `id`, `name` FROM `test`.`tbl_test` WHERE `name` IN (SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA` );
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | information_schema |
|  2 | test               |
+----+--------------------+
2 rows in set (0.00 sec)