Percona Server moved to https://jira.percona.com/projects/PS

Incorrect results on partitioned table with index_merge enabled

Bug #1234318 reported by Brian Franklin on 2013-10-02

This bug affects 1 person

	Status	Importance	Assigned to
MySQL Server	Unknown	Unknown	mysql-bugs #69581
Percona Server moved to https://jira.percona.com/projects/PS	Incomplete	High	Unassigned
5.1	Invalid	Undecided	Unassigned
5.5	Invalid	Undecided	Unassigned
5.6	Incomplete	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';

Tags:

Revision history for this message

Brian Franklin (brqan) wrote on 2013-10-02:

Example DB and table with data Edit (272.0 KiB, application/zip)

Revision history for this message

Valerii Kravchuk (valerii-kravchuk) wrote on 2013-10-03:

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

Changed in percona-server:
status:	New → Incomplete

Revision history for this message

Brian Franklin (brqan) wrote on 2013-10-03:

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.

Valerii Kravchuk (valerii-kravchuk) on 2013-10-04

Changed in percona-server:
status:	Incomplete → New
tags:	added: upstream

Revision history for this message

Valerii Kravchuk (valerii-kravchuk) wrote on 2013-10-04:

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)

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...

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)

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');
Query OK, 1 row affected (0.09 sec)

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

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

mysql> SELECT @@optimizer_switch;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=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 |
+------+------+---------------------+
2 rows in set (0.34 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_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=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)

Revision history for this message

Laurynas Biveinis (laurynas-biveinis) wrote on 2014-07-31:

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?

Revision history for this message

Brian Franklin (brqan) wrote on 2016-02-04:

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.

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-25:

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-729