| 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: | |
| 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 10:15]
MySQL Verification Team
Hello William, Thank you for the report. Thanks, Umesh
[29 May 2015 10:15]
MySQL Verification Team
// 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]
MySQL Verification Team
// 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)
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)