Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.33a
-
Fix Version/s: 5.5.37
-
Component/s: None
-
Labels:None
Description
Even after fix for MDEV-5112 (and MySQL Bug#69581), a query that runs index_merge intersection (union is probably affected too) over partitioned table, may return wrong query result.
Testcase:
create table t11 (
a int not null,
b int not null,
pk int not null,
primary key (pk),
key(a),
key(b)
) partition by hash(pk) partitions 10;
insert into t11 values (1,2,4); -- both
insert into t11 values (1,0,17); -- left
insert into t11 values (1,2,25); -- both
insert into t11 values (10,20,122);
insert into t11 values (10,20,123);
-- Now, fill in some data so that the optimizer choses index_merge
create table t12 (a int);
insert into t12 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into t11 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t12 A, t12 B, t12 C;
insert into t11 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
10+A.a + 10*B.a + 100*C.a + 1000*D.a,
2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
from t12 A, t12 B, t12 C ,t12 D;
-- This should show index_merge, using intersect
explain select * from t11 where a=1 and b=2 and pk between 1 and 999999 ;
-- 794 rows in output
select * from t11 where a=1 and b=2 and pk between 1 and 999 ;
-- 802 rows in output
select * from t11 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ;
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
== Ideas on how this could be fixed ==
Ordered index scans over ha_partition return records in the order of (key, extended_key_parts, ...).
There is no way to change that.
It could be nice to get index_merge to inform the storage engine that it needs the index scans to be rowid-ordered but not key-ordered, but this is a too big change for a GA version.
A possible solution:
index scan on ha_partition must return records ordered by
key_value, ext_key_parts (if there are any)
When ROR-index_merge is used, the key_value part is fixed, which means that
index scans will return rows ordered by:
ext_key_parts (if there are any)
On the other hand, ROR-index_merge requires that rows come in the order that
matches the ordering imposed by ha_partition::cmp_ref().
The solution is:
1. Let index scan on ha_partition return rows ordered by
key_rec_cmp, then part_id - for tables with extended keys (we do this now)
key_rec_cmp, then underlying_rowid, then part_id - for tables without extended keys.
2. Let ha_partition::cmp_ref() compare rowids by comparing underlying_rowid
first, then comparing part_id.
Committed another fix. I'll need a review for it.
Igor Babaev, as you have requested: please find the attached testcase (mdev5555-innodb.test) which fails in MySQL 5.6.
Fix pushed into 5.5 tree
It seems it's ok with MySQL 5.6.16, I obtain 802 rows in either cases.
MariaDB 5.5.35 still returns 794 and 802.