[MDEV-5112] InnoDB index intersection returns less results than expected Created: 2013-10-08  Updated: 2014-04-22  Resolved: 2013-10-24

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.5.33a
Fix Version/s: 5.5.34, 10.0.6

Type: Bug Priority: Major
Reporter: Arnaud Gadal Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
Labels: None
Environment:

Debian GNU/Linux 6.0

Issue Links:
Duplicate
is duplicated by MDEV-5102 MySQL Bug 69581 Closed
Relates
relates to MDEV-6151 Wrong result (missing row) with LEFT ... Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2013-10-08 ]

Thanks for the report

Comment by Igor Babaev [ 2013-10-19 ]

Version 5.1,5.2,5.3 of MariaDB are not affected.

Comment by Igor Babaev [ 2013-10-20 ]

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).

Comment by Igor Babaev [ 2013-10-20 ]

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.

Comment by Sergei Petrunia [ 2013-10-23 ]

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.

Comment by Sergei Petrunia [ 2013-10-23 ]

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.

Comment by Sergei Petrunia [ 2013-10-23 ]

MariaDB's counterpart for http://bugs.mysql.com/bug.php?id=70703 is MDEV-5177.

Comment by Sergei Petrunia [ 2013-10-24 ]

Pushed the fix for this particular bug (but not for MDEV-5177) into MariaDB 5.5

Generated at Thu Feb 08 07:01:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.