enterprise-support team mailing list archive
-
enterprise-support team
-
Mailing list archive
-
Message #02847
[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
