Bug #69581 InnoDB index intersection returns less results than expected
Submitted: 26 Jun 2013 10:04 Modified: 7 Oct 2013 10:56
Reporter: Putinas Piliponis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.12-log Source distribution OS:Any
Assigned to: Aditya A
Tags: INDEX, innodb, intersect

[26 Jun 2013 10:04] Putinas Piliponis
Description:
Simple query using AND. Optimizer is going to use index intersection.

mysql> explain SELECT agentaccountid,termid FROM moneyTransfer  WHERE agentaccountid = 35160 AND termid = 14780 limit 1000;
+----+-------------+---------------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------------------+
| id | select_type | table         | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                                            |
+----+-------------+---------------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------------------+
|  1 | SIMPLE      | moneyTransfer | index_merge | agentAccountId,termId | agentAccountId,termId | 3,3     | NULL |   43 | Using intersect(agentAccountId,termId); Using where; Using index |
+----+-------------+---------------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------------------+
1 row in set (0.01 sec)

Executing select, however only two rows are returned.

mysql> SELECT agentaccountid,termid FROM moneyTransfer  WHERE agentaccountid = 35160 AND termid = 14780 limit 10;
+----------------+--------+
| agentaccountid | termid |
+----------------+--------+
|          35160 |  14780 |
|          35160 |  14780 |
+----------------+--------+
2 rows in set (0.06 sec)

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

Here with disabled optimizer - full data set is returned as expected.

mysql> SELECT agentaccountid,termid FROM moneyTransfer  WHERE agentaccountid = 35160 AND termid = 14780 limit 10;
+----------------+--------+
| agentaccountid | termid |
+----------------+--------+
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
|          35160 |  14780 |
+----------------+--------+
10 rows in set (0.01 sec)

How to repeat:
I couldn't write full test case. On smaller data sample index intersection worked correctly. My current db is around 7Gb size.
[26 Jun 2013 14:49] Miguel Solorzano
Thank you for the bug report. Please provide the create table statement. Thanks.
[27 Jun 2013 10:28] Putinas Piliponis
Provided. In private post though - Slightly sensitive information.
[10 Jul 2013 15:02] Vitaly Veksler
I have the same bug on mysql 5.6.12
table size can be less than 1GB to reproduce the bug.
[10 Jul 2013 15:05] Vitaly Veksler
CentOS 6 x64
[31 Jul 2013 14:22] Miguel Solorzano
Could you please provide the dump file with insert data too? . Thanks.
[9 Aug 2013 8:38] Putinas Piliponis
Sorry, I wish I could, but the data contains private date which I cannot share.
[28 Aug 2013 5:42] 朱 先生
I have the same problem
Version:5.6.13

table use
/*!50100 PARTITION BY HASH ( city)                                                                                   
                PARTITIONS 30 */   
1	SIMPLE	biz_merchant_2	index_merge	manauser,merchant_category	manauser,merchant_category	75,75	\N	1	Using intersect(manauser,merchant_category); Using where

---
only return city=1's records
[28 Aug 2013 7:14] Putinas Piliponis
朱 先生, can you please upload whole table as developers weren't able to recreate the issue?
[24 Sep 2013 2:57] Andrew Dalgleish
Verified with a small test case in 5.6.11, 5.6.13.
[24 Sep 2013 2:59] Andrew Dalgleish
test case

Attachment: testcase-indexmerge.sql (application/octet-stream, text), 1.50 KiB.

[24 Sep 2013 3:00] Andrew Dalgleish
test case result

Attachment: testcase-indexmerge.sql.txt (text/plain), 1.21 KiB.

[4 Oct 2013 10:10] Valerii Kravchuk
It seems 5.5.32 is NOT affected (see below), so why don't we have "regression" tag here?

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.5.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> USE test
Database changed
mysql> DROP TABLE IF EXISTS `table1`;
Query OK, 0 rows affected, 1 warning (0.27 sec)

mysql> CREATE TABLE `table1` (
    ->   `col1` bigint(20) unsigned NOT NULL ,
    ->   `col2` bigint(20) unsigned NOT NULL ,
    ->   `col3` datetime NOT NULL ,
    ->   PRIMARY KEY (`col3`),
    ->   KEY (`col1`),
    ->   KEY (`col2`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    ->  PARTITION BY RANGE (TO_DAYS(col3))
    -> (
    ->  PARTITION p_20130310 VALUES LESS THAN (735303) ENGINE = InnoDB,
    ->  PARTITION p_20130311 VALUES LESS THAN (735304) ENGINE = InnoDB,
    ->  PARTITION p_20130312 VALUES LESS THAN (735305) ENGINE = InnoDB
    -> );
Query OK, 0 rows affected (2.31 sec)

mysql> INSERT INTO `table1` VALUES (2,96,'2013-03-08 16:28:05');
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:47:39');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:50:27');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:04');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:24');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO `table1` VALUES (2,2,'2013-03-12 10:11:48');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> SET optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT @@optimizer_switch;
+-------------------------------------------------------------------------------
-----------------------------------------+
| @@optimizer_switch
                                         |
+-------------------------------------------------------------------------------
-----------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_inte
rsection=on,engine_condition_pushdown=on |
+-------------------------------------------------------------------------------
-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
    ->     AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
    -> GROUP BY 1, 2, 3;
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
|    1 |    2 | 2013-03-08 16:47:39 |
|    1 |    2 | 2013-03-08 16:50:27 |
|    1 |    2 | 2013-03-11 16:33:04 |
|    1 |    2 | 2013-03-11 16:33:24 |
+------+------+---------------------+
4 rows in set (1.06 sec)

mysql> EXPLAIN SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
    ->     AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
    -> GROUP BY 1, 2, 3;
+----+-------------+--------+-------------+-------------------+-----------+-----
----+------+------+-------------------------------------------------------------
---------+
| id | select_type | table  | type        | possible_keys     | key       | key_
len | ref  | rows | Extra
         |
+----+-------------+--------+-------------+-------------------+-----------+-----
----+------+------+-------------------------------------------------------------
---------+
|  1 | SIMPLE      | table1 | index_merge | PRIMARY,col1,col2 | col1,col2 | 8,8
    | NULL |    2 | Using intersect(col1,col2); Using where; Using index; Using
filesort |
+----+-------------+--------+-------------+-------------------+-----------+-----
----+------+------+-------------------------------------------------------------
---------+
1 row in set (0.00 sec)

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

mysql> SELECT @@optimizer_switch;
+-------------------------------------------------------------------------------
------------------------------------------+
| @@optimizer_switch
                                          |
+-------------------------------------------------------------------------------
------------------------------------------+
| index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_int
ersection=on,engine_condition_pushdown=on |
+-------------------------------------------------------------------------------
------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
    ->     AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
    -> GROUP BY 1, 2, 3;
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
|    1 |    2 | 2013-03-08 16:47:39 |
|    1 |    2 | 2013-03-08 16:50:27 |
|    1 |    2 | 2013-03-11 16:33:04 |
|    1 |    2 | 2013-03-11 16:33:24 |
+------+------+---------------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
    ->     AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
    -> GROUP BY 1, 2, 3;
+----+-------------+--------+------+-------------------+------+---------+-------
+------+-----------------------------+
| id | select_type | table  | type | possible_keys     | key  | key_len | ref
| rows | Extra                       |
+----+-------------+--------+------+-------------------+------+---------+-------
+------+-----------------------------+
|  1 | SIMPLE      | table1 | ref  | PRIMARY,col1,col2 | col1 | 8       | const
|    4 | Using where; Using filesort |
+----+-------------+--------+------+-------------------+------+---------+-------
+------+-----------------------------+
1 row in set (0.00 sec)
[7 Oct 2013 10:54] Jørgen Løland
Posted by developer:
 
This bug has already been in fixed in 5.6.14 by duplicate internal bug and documented with the following changelog entry.

Noted in 5.6.14, 5.7.3 changelogs.

For partitioned tables, queries could return different results
depending on whether Index Merge was used.
[23 Oct 2013 14:39] Sergey Petrunya
The fix is incomplete. See Bug #70703.