Details
Major
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)
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Version 5.1,5.2,5.3 of MariaDB are not affected.
This problem appeared in the MariaDB 5.5 tree right after the merge with MySQL 5.5.29 in revision 3624 (rev 3623 was not yet affected).
Here are my findings.
The function QUICK_RANGE_SELECT::init_ror_merged_scan() calls QUICK_RANGE_SELECT::reset()
for each merged ROR scan.
QUICK_RANGE_SELECT::reset() calls handler::ha_index_init() for each merged scan.
handler::ha_index_init() calls ha_partition::index_init as each scan is an index scan for partitioned table.
For the first scan the value of m_pkey_is_clustered is true, while for the second scan the value of this member is false.
It happens because for the second scan a clone of the partition handler is created with the copy constructor
ha_partition::ha_partition(handlerton *hton, TABLE_SHARE *share, partition_info *part_info_arg,
ha_partition *clone_arg, MEM_ROOT *clone_mem_root_arg)
that forgets to copy the value of m_pkey_is_clustered and the value remains false.
As a result for the second merged scan PK is not taken into account when comparing keys.
The following patch from the MySQL 5.6 code (rev 5366)
=== modified file 'sql/ha_partition.cc'
— sql/ha_partition.cc 2013-01-15 18:13:32 +0000
+++ sql/ha_partition.cc 2013-10-20 03:19:58 +0000
@@ -224,6 +224,7 @@ ha_partition::ha_partition(handlerton *h
m_is_sub_partitioned= m_part_info->is_sub_partitioned();
m_is_clone_of= clone_arg;
m_clone_mem_root= clone_mem_root_arg;
+ m_pkey_is_clustered= clone_arg->primary_key_is_clustered();
DBUG_VOID_RETURN;
}
resolves the problem,
What remains unclear for me is why this test case does not fail for MySQL 5.5 that does not have this patch applied.
Maybe it can be explained by the fact that handler::read_multi_range_first ()is called with the parameter sorted == false?
(In MariaDB 5.5 the corresponding parameter is set to true).
I tried to investigate this, but MySQL 5.5 and MariDB 5.5 diverged too much in the MRR code.
Why we don't see the problem in MariaDB 5.3 is more or less clear: we have some code there that was removed in 5.5 when merging
with MySQL 5.5.29.
The fix in mysql-5.6 is incomplete. I've filed http://bugs.mysql.com/bug.php?id=70703 which demonstrates how to get the wrong result again.
I have investigated why mysql-5.5 doesn't produce a wrong result. It has
quick->sorted= false
handler::m_ordered= false
The first of the merged QUICK_RANGE_SELECTs has
ha_partition::m_pkey_is_clustered= true (correct)
The second of the mergeed QUICK_RANGE_SELECTs has
ha_partition::m_pkey_is_clustered= false (wrong)
However, this wrong value is not a problem because handler::m_ordered=false. When no ordered output is requested, ha_partition does a variant of index scan that produces records in no particular order (e.g. it uses handle_unordered_scan_next_partition). That variant doesn't care about value of m_pkey_is_clustered.
In post-MRR versions (MariaDB 5.5+, MySQL 5.6+) merged QUICK_RANGE_SELECTs have mrr_is_output_sorted=true. The code in ha_partition that does index scan and produces ordered output does depend on m_pkey_is_clustered.
MariaDB's counterpart for http://bugs.mysql.com/bug.php?id=70703 is MDEV-5177.
Pushed the fix for this particular bug (but not for MDEV-5177) into MariaDB 5.5
Thanks for the report