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:
None 
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:31] Sergey Petrunya
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)
[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] Umesh Shastry
Hello Sergey,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh