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
-
MDEV-5821 Wrong result of SELECT query while two indexed colums are in WHERE section
- Closed