MySQL Bugs: #79675: index_merge_intersection optimization causes wrong query results

Bug #79675	index_merge_intersection optimization causes wrong query results
Submitted:	16 Dec 2015 16:48	Modified:	19 May 2017 18:43
Reporter:	Saverio Miroddi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.7/8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When the `index_merge_intersection` optimization strategy is enabled (which is the default), a certain query against an InnoDB table gives wrong results (specifically, empty resultset).

This behavior was reproducible 100% of the times. After around a week, I've run an ANALYZE table, and the issue was gone. In the meanwhile though, I've executed the query optimizer tracing, which I attach. I also attach the plan.

While the issue was present, when I tried to copy the table to a copy, and execute the query against the copy, I wasn't able to reproduce the issue.

How to repeat:
See comments.

Are you able to provide a dump file (create table + insert data)?. Thanks.

Hello, unfortunately, the data is not available anymore.

I've just noticed that the comment:

    [...] when I tried to copy the table to a copy, and execute the query against the copy, I wasn't able to reproduce the issue.

Should be:

    [...] when I tried to create a copy of the table, and execute the query against the copy, I wasn't able to reproduce the issue.

Yet another argument for using a composite index instead of depending on index_merge_intersection ?

> Yet another argument for using a composite index instead of depending on index_merge_intersection ?

The query doesn't depend on this optimization; independently accessing the available indices is enough.

i also encountered the same issue also the query uses index_intersect and if i force a certain index it provides the desired resultset, otherwise an empty resultset is returned.

Server version: 5.6.29 MySQL Community Server (GPL)
Centos kernel 2.6.32-573.22.1.el6.x86_64

disabling index_merge_intersection solves the problem and at least benchmarking my query it also improves performance a little bit.

without with index_merge_intersect		
concurent: 50
iterations: 100
Benchmark
        Average number of seconds to run all queries: 0.013 seconds
        Minimum number of seconds to run all queries: 0.012 seconds
        Maximum number of seconds to run all queries: 0.017 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

with index_merge_intersect		
concurent: 50
iterations: 100
Benchmark
        Average number of seconds to run all queries: 0.016 seconds
        Minimum number of seconds to run all queries: 0.015 seconds
        Maximum number of seconds to run all queries: 0.021 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

forgot to mention, that to disable index_merge_intersection in my.cnf you need to specify:
optimizer_switch=index_merge_intersection=off

I can confirm this bug is still present in 5.7

The following query, which should return a single row, returns empty:

Select record #100 from main table and perform an exists subquery:

SELECT * 
FROM main_table
WHERE id = 100
AND EXISTS (
    SELECT NULL FROM main_table t
        LEFT OUTER JOIN subtable ON subtable.main_table_id = t.id
        WHERE subtable.my_column= 10
        AND t.id = main_table.id #scope to parent
)

In MySQL 5.5, the query works properly and a single row (id = 100) is returned.

In MySQL 5.7 the same query incorrectly returns no results.

This issue is caused/effected by the MySQL optimizer switch: index_merge_intersection.

If I change this setting to OFF:

SET optimizer_switch="index_merge_intersection=off";

the query then works as expected in 5.7

There are individual indexes on subtable.maint_table_id and subtable.my_column

EXPLAIN shows intersection of these 2 indexes.  If I create an index hint: 

LEFT OUTER JOIN subtable USE INDEX(main_table_id)

The intersection optimization disappears and the correct result is returned.

Also, If I change the outer WHERE from

WHERE id = 100

to

WHERE id BETWEEN 100 and 101

then the correct results are also returned.

Hi!

I'm also faced with this problem in 5.7

Steps to reproduce:

CREATE TABLE `table1` (
  `ID` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

CREATE TABLE `table2` (
  `ID` bigint(20) NOT NULL,
  `c1` bigint(20) DEFAULT NULL,
  `c2` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `c1_INDEX` (`c1`),
  KEY `c2_INDEX` (`c2`)
) ENGINE=InnoDB;

insert into table1 values (1),(2);
insert into table2 values (1,1,20),(2,1,20),(3,1,30),(4,2,20),(5,2,20),(6,2,30);

mysql> select * from table1 t1 where exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

But the following query which should return (1) returns empty set:

mysql> select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
Empty set (0.00 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.15-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)

Thanks Yegor Strelkov for the test case:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.17 Source distribution PULL: 2016-OCT-03

Copyright (c) 2000, 2016, 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 5.7 > CREATE DATABASE xbug
    -> ;
Query OK, 1 row affected (0.02 sec)

mysql 5.7 > USE xbug
Database changed
mysql 5.7 > CREATE TABLE `table1` (
    ->   `ID` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE `table2` (
    ->   `ID` bigint(20) NOT NULL,
    ->   `c1` bigint(20) DEFAULT NULL,
    ->   `c2` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `c1_INDEX` (`c1`),
    ->   KEY `c2_INDEX` (`c2`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql 5.7 >
mysql 5.7 > insert into table1 values (1),(2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.7 > insert into table2 values (1,1,20),(2,1,20),(3,1,30),(4,2,20),(5,2,20),(6,2,30);
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.7 > select * from table1 t1 where exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.03 sec)

mysql 5.7 > select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
Empty set (0.03 sec)

mysql 5.7 > SET optimizer_switch="index_merge_intersection=off";
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.7 > SET optimizer_switch="index_merge_intersection=on";
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
Empty set (0.00 sec)

Thank you for the bug report. With current source server I was able to repeat only with 5.7 server and not anymore with 5.6:

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 Source distribution PULL: 2016-OCT-03

Copyright (c) 2000, 2016, 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 5.6 > CREATE DATABASE xbug;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > USE xbug
Database changed
mysql 5.6 > CREATE TABLE `table1` (
    ->   `ID` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE `table2` (
    ->   `ID` bigint(20) NOT NULL,
    ->   `c1` bigint(20) DEFAULT NULL,
    ->   `c2` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `c1_INDEX` (`c1`),
    ->   KEY `c2_INDEX` (`c2`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.28 sec)

mysql 5.6 >
mysql 5.6 > insert into table1 values (1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 > insert into table2 values (1,1,20),(2,1,20),(3,1,30),(4,2,20),(5,2,20),(6,2,30);
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.6 >  select * from table1 t1 where exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.02 sec)

mysql 5.6 > select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.6 > SET optimizer_switch="index_merge_intersection=on";
Query OK, 0 rows affected (0.06 sec)

mysql 5.6 > select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Server version 8.0 affected too:

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 8.0 > CREATE DATABASE xbug;
Query OK, 1 row affected (0.07 sec)

mysql 8.0 > USE xbug
Database changed
mysql 8.0 > CREATE TABLE `table1` (
    ->   `ID` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql 8.0 >
mysql 8.0 > CREATE TABLE `table2` (
    ->   `ID` bigint(20) NOT NULL,
    ->   `c1` bigint(20) DEFAULT NULL,
    ->   `c2` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `c1_INDEX` (`c1`),
    ->   KEY `c2_INDEX` (`c2`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.27 sec)

mysql 8.0 >
mysql 8.0 > insert into table1 values (1),(2);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 8.0 > insert into table2 values (1,1,20),(2,1,20),(3,1,30),(4,2,20),(5,2,20),(6,2,30);
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 8.0 > select * from table1 t1 where exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.02 sec)

mysql 8.0 > select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
Empty set (0.02 sec)

mysql 8.0 > SET optimizer_switch="index_merge_intersection=off";
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > select * from table1 t1 where t1.ID=1 and exists (select 1 from table2 t2 where t2.c2=30 and t2.c1=t1.ID);
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Posted by developer:
 
Noted in 5.7.19, 8.0.2 changelog.

Certain queries against InnoDB tables that used a primary key and a
subquery could return incorrect results if the
index_merge_intersection flag of the optimization_switch system
variable was enabled.

This exists in 5.6; will this be patched?