| Bug #71914 | Wrong query result when using INFORMATION_SCHEMA tables and semi-join subqueries | ||
|---|---|---|---|
| Submitted: | 3 Mar 2014 13:31 | Modified: | 4 Mar 2014 6:11 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 5.6.16-debug-log | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Mar 2014 13:32]
Sergey Petrunya
EXPLAIN for the problematic query:
mysql> explain SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM files WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM files F2 WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME;
+----+--------------+-------------+--------+---------------+------------+---------+-----------------------------+------+--------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+---------------+------------+---------+-----------------------------+------+--------------------------------------------------------------------------------------------+
| 1 | SIMPLE | files | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 195 | db.files.LOGFILE_GROUP_NAME | 1 | NULL |
| 2 | MATERIALIZED | F2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | MATERIALIZED | PARTITIONS | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Skip_open_table; Scanned all databases; Using join buffer (Block Nested Loop) |
+----+--------------+-------------+--------+---------------+------------+---------+-----------------------------+------+--------------------------------------------------------------------------------------------+
[3 Mar 2014 13:33]
Sergey Petrunya
As far as I could understand from debugging, the P_S code never attempts to fill the temporary table for I_S.PARTITIONS.
[3 Mar 2014 13:59]
Sergey Petrunya
Changed category s/peroformance_schema/information_schema/
[3 Mar 2014 14:01]
Sergey Petrunya
Fix typo in bug description, s/PERFORMANCE_SCHEMA/INFORMATION_SCHEMA/
[4 Mar 2014 6:11]
MySQL Verification Team
Hello Sergey, Thank you for the bug report. Verified as described. Thanks, Umesh
Description: Queries that use PERFORMANCE_SCHEMA and semi-joins (I suspect SJ-Materialization in particular) may return wrong results. I've discovered this by looking at the code. The following example query looks artificial, but I suspect one can construct meaningful queries that will return wrong results. How to repeat: Run this: create database db; use db; CREATE TABLE t1 (i INT) ENGINE=MyISAM PARTITION BY HASH(i) PARTITIONS 16; CREATE TABLE t2 (i INT) ENGINE=MyISAM PARTITION BY HASH(i) PARTITIONS 16; CREATE TABLE t3 (i INT) ENGINE=MyISAM PARTITION BY HASH(i) PARTITIONS 16; create table files as select * from information_schema.files; insert into files (file_id, file_type, file_name, logfile_group_name, TABLESPACE_NAME) values (1, 'UNDO LOG', 'file1', 'logfile-group1', 'tablespace1'); insert into files (file_id, file_type, file_name, logfile_group_name, TABLESPACE_NAME) values (2, 'DATAFILE', 'file1', 'logfile-group1', 'tablespace1'); update files set tablespace_name='p11'; Then: mysql> mysql> set optimizer_switch='semijoin=on'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM files WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM files F2 WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME; Empty set (0.00 sec) mysql> mysql> set optimizer_switch='semijoin=off'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM files WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM files F2 WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME; +--------------------+-----------+---------------+--------------+--------+-------+ | LOGFILE_GROUP_NAME | FILE_NAME | TOTAL_EXTENTS | INITIAL_SIZE | ENGINE | EXTRA | +--------------------+-----------+---------------+--------------+--------+-------+ | logfile-group1 | file1 | NULL | NULL | | NULL | +--------------------+-----------+---------------+--------------+--------+-------+ 1 row in set (0.01 sec)