← Back to team overview

enterprise-support team mailing list archive

[Bug 1234318] Re: Incorrect results on partitioned table with index_merge enabled

 

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');
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> 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_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)

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)

** Changed in: percona-server/5.6
       Status: New => Confirmed

** Changed in: percona-server/5.5
       Status: New => Invalid

** Changed in: percona-server/5.1
       Status: New => Invalid

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1234318

Title:
  Incorrect results on partitioned table with index_merge enabled

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1234318/+subscriptions