Incorrect results on partitioned table with index_merge enabled

Bug #1234318 reported by Brian Franklin on 2013-10-02
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
High
Unassigned
5.1
Undecided
Unassigned
5.5
Undecided
Unassigned
5.6
High
Unassigned

Bug Description

The table has several indices and is partitioned by a date column.

When querying the table with index_merge (probably specifically index_merge_intersection) on, there are 0 rows returned (incorrect).
When querying the table with index_merge (probably specifically index_merge_intersection) off, there is 1 row returned (correct).

If you remove the partitioning from the table, then you always get 1 row returned (correct), regardless of whether index_merge is enabled or not.

Running on: Percona Server with XtraDB (GPL), Release rc60.4, Revision 393

Use attached example database with the following queries:

SET SESSION OPTIMIZER_SWITCH='index_merge=on';

SELECT * FROM EXAMPLE
WHERE 1=1
AND CAMPAIGN_ID='00006170'
AND TEST='N'
AND AUTO='N'
AND MOST_RECENT='Y'
AND ISSUE='10';

SET SESSION OPTIMIZER_SWITCH='index_merge=off';

SELECT * FROM EXAMPLE
WHERE 1=1
AND CAMPAIGN_ID='00006170'
AND TEST='N'
AND AUTO='N'
AND MOST_RECENT='Y'
AND ISSUE='10';

Brian Franklin (brqan) wrote :

It can be a duplicate of upstream http://bugs.mysql.com/bug.php?id=69581. Please, check.

Changed in percona-server:
status: New → Incomplete
Brian Franklin (brqan) wrote :

It's certainly possible. I ran the test case attached in the comments of the upstream ticket. The behavior seems similar.

I don't know if I can say for sure if the underlying cause is the same until the upstream is fixed, and I can retest my case with a patched version.

Changed in percona-server:
status: Incomplete → New
tags: added: upstream
Download full text (9.5 KiB)

Test from the upstream bug shows the failure:

[openxs@chief p5.6]$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13-debug MySQL Community Server (GPL)

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
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
DROP TABLE IF EXISTS `table1`;
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))
(
Database changed
mysql> DROP TABLE IF EXISTS `table1`;
Query OK, 0 rows affected, 1 warning (0.00 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
    -> );
INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:47:39');
INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:50:27');
INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:04');
INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:24');
INSERT INTO `table1` VALUES (2,2,'2013-03-12 10:11:48');

SET optimizer_switch='index_merge=on';
SELECT @@optimizer_switch;
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;
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;

SET optimizer_switch='index_merge=off';
SELECT @@optimizer_switch;
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;
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;
Query OK, 0 rows affected (2.54 sec)

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

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

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

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

mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:24');
Qu...

Read more...

All three related upstream bugs (http://bugs.mysql.com/bug.php?id=70588, http://bugs.mysql.com/bug.php?id=70703, http://bugs.mysql.com/bug.php?id=69581) are fixed as of MySQL 5.6.16.

Does the issue still occur on the latest Percona Server?

Brian Franklin (brqan) wrote :

I test this against Percona-Server-5.6.28-rel76.1-Linux.x86_64.ssl098e

version_comment:
Percona Server (GPL), Release 76.1, Revision 5759e76

The original test posted here now yields the same (correct) results of 1 row in the result sets of both queries, so it appears that this has been fixed.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.