MySQL Bugs: #70588: Index merge used on partitionned table can return wrong result set

Bug #70588	Index merge used on partitionned table can return wrong result set
Submitted:	10 Oct 2013 12:09	Modified:	5 Nov 2013 17:05
Reporter:	Joffrey MICHAIE	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.6.14,5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	index merge, intersect, partition, wrong resultset

Description:
Simple query where 

indexed_col = 'x' and indexed_date_column = 'yyyy-mm-aa'

 returns wrong resultset, when run on partitionned table, and using index_merge (indexed_col,indexed_date_column)

Found on 5.5, repeated on 5.6.14, didn't test on 5.1

How to repeat:
Start MySQL with 100% default settings.

CREATE TABLE `poll` (
  `id_key` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id` int(6) NOT NULL DEFAULT '0',
  `id_poll` int(6) NOT NULL DEFAULT '0',
  `date_long` datetime NOT NULL,
  `date_short` date NOT NULL,
  PRIMARY KEY (`id_key`,`id_poll`),
  KEY `id` (`id`),
  KEY `date_creation` (`date_short`)
) ENGINE=InnoDB AUTO_INCREMENT=14101389 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (id_poll)
PARTITIONS 20 */;

INSERT INTO `poll` VALUES (NULL,1718848,580660,'2013-10-09 18:21:00','2013-10-09');
INSERT INTO `poll` VALUES (NULL,39369,869049,'2013-10-09 18:21:02','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,1916580,'2013-10-09 18:21:09','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,1333222,'2013-10-09 18:20:31','2013-10-09');
INSERT INTO `poll` VALUES (NULL,1557405,869555,'2013-10-09 18:20:32','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,1343938,'2013-10-09 18:20:32','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,1157259,'2013-10-09 18:20:40','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,1800441,'2013-10-09 18:20:50','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,60,'2013-10-09 18:20:53','2013-10-09');
INSERT INTO `poll` VALUES (NULL,1718848,580660,'2013-10-09 18:21:00','2013-10-09');
INSERT INTO `poll` VALUES (NULL,39369,869049,'2013-10-09 18:21:02','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,1916580,'2013-10-09 18:21:09','2013-10-09');
INSERT INTO `poll` VALUES (NULL,70,1916580,'2013-10-0Query OK, 1 row affected (0,01 sec)

explain SELECT date_short FROM poll WHERE id =70 AND date_short =  '2013-10-10';                                                                                                                                                      +----+-------------+-------+-------------+------------------+------------------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table | type        | possible_keys    | key              | key_len | ref  | rows | Extra                                                       |
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+-------------------------------------------------------------+
|  1 | SIMPLE      | poll  | index_merge | id,date_creation | date_creation,id | 3,4     | NULL |    2 | Using intersect(date_creation,id); Using where; Using index |
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+-------------------------------------------------------------+
1 row in set (0,00 sec)

(data sample is small, you can run ANALYZE TABLE if intersect is not shown)

mysql>  SELECT date_short FROM poll WHERE id =70 AND date_short =  '2013-10-10';
Empty set (0,01 sec)

mysql>  SELECT date_short FROM poll WHERE id =70 AND date_short like  '2013-10-10';
+------------+
| date_short |
+------------+
| 2013-10-10 |
+------------+
1 row in set (0,00 sec)

Other examples:
mysql>  SELECT date_short FROM poll IGNORE INDEX (date_creation) WHERE id =70 AND date_short =  '2013-10-10';
+------------+
| date_short |
+------------+
| 2013-10-10 |
+------------+
1 row in set (0,00 sec)

mysql>  SELECT date_short FROM poll IGNORE INDEX (id) WHERE id =70 AND date_short =  '2013-10-10';
+------------+
| date_short |
+------------+
| 2013-10-10 |
+------------+
1 row in set (0,00 sec)

mysql> alter table poll remove partitioning;
Query OK, 13 rows affected (0,08 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql>  SELECT date_short FROM poll WHERE id =70 AND date_short =  '2013-10-10';
+------------+
| date_short |
+------------+
| 2013-10-10 |
+------------+
1 row in set (0,00 sec)

Suggested fix:
Return correct resultset, or do not use intersection merge on hash partitionned tables

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.15 Source distribution

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 5.6 > use xd
Database changed
mysql 5.6 > CREATE TABLE `poll` (
    ->   `id_key` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `id` int(6) NOT NULL DEFAULT '0',
    ->   `id_poll` int(6) NOT NULL DEFAULT '0',
    ->   `date_long` datetime NOT NULL,
    ->   `date_short` date NOT NULL,
    ->   PRIMARY KEY (`id_key`,`id_poll`),
    ->   KEY `id` (`id`),
    ->   KEY `date_creation` (`date_short`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=14101389 DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY HASH (id_poll)
    -> PARTITIONS 20 */;
Query OK, 0 rows affected (4.76 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO `poll` VALUES (NULL,1718848,580660,'2013-10-09 18:21:00','2013-10-09');
Query OK, 1 row affected (0.20 sec)

<CUT>

mysql 5.6 > SELECT date_short FROM poll WHERE id =70 AND date_short =  '2013-10-10';
Empty set (0.00 sec)

mysql 5.6 > alter table poll remove partitioning;
Query OK, 13 rows affected (3.76 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql 5.6 > SELECT date_short FROM poll WHERE id =70 AND date_short =  '2013-10-10';
+------------+
| date_short |
+------------+
| 2013-10-10 |
+------------+
1 row in set (0.02 sec)

mysql 5.6 >

From:
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-14.html

For partitioned tables, queries could return different results depending on whether Index Merge was used. (Bug #16862316)

Fixed in 5.5+. Documented in the 5.5.36, 5.6.16, and 5.7.4 changelogs, as follows:

      Queries using index_merge optimization (see 
      http://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html) 
      could return invalid results when run against tables that were 
      partitioned by HASH. 

Closed.

5.5$ bzr log -r 4538
------------------------------------------------------------
revno: 4538
committer: Aditya A <aditya.a@oracle.com>
branch nick: mysql-5.5
timestamp: Tue 2013-11-05 19:25:26 +0530
message:
  Bug#17588348: INDEX MERGE USED ON PARTITIONED TABLE 
                 CAN RETURN WRONG RESULT SET
  
  PROBLEM
  -------
  In ha_partition::cmp_ref() we were only calling the 
  underlying cmp_ref() of storage engine if the records
  are in the same partiton,else we sort by partition and
  returns the result.But the index merge intersect 
  algorithm expects first to sort by row-id first and 
  then by partition id.
  
  FIX
  ---
  Compare the refernces first using storage engine cmp_ref
  and then if references are equal(only happens if 
  non clustered index is used) then sort it by partition id.
  
  [Approved by Mattiasj #rb3755]
  -