Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.5.33a
-
None
-
None
-
Debian GNU/Linux 6.0
Description
This bug affects MariaDB 5.5.3x perhaps older versions too.
This bug concerns also all MySQL 5.6 versions (>= 5.6.8), it will be fixed on 5.6.14 : http://bugs.mysql.com/bug.php?id=69581
The test case below comes from the above link :
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)) |
(
|
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 (2,96,'2013-03-08 16:28:05'); |
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; |
With index_merge=on you obtain :
+------+------+---------------------+
|
| col1 | col2 | col3 |
|
+------+------+---------------------+
|
| 1 | 2 | 2013-03-08 16:47:39 |
|
| 1 | 2 | 2013-03-08 16:50:27 |
|
+------+------+---------------------+
|
2 rows in set (0.00 sec)
|
but you should obtain what you have when you disable index_merge :
+------+------+---------------------+
|
| 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)
|