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

  1. Bug #1234318
  2. Comment #4

Comment 4 for bug 1234318

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

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)