Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.33a
-
None
-
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 ;
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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.
igor, as you have requested: please find the attached testcase (mdev5555-innodb.test) which fails in MySQL 5.6.
== 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.