We're updating the issue view to help you get more done.Learn more

ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 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 ;

Status

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

Fix versions

Affects versions

5.5.33a

Priority

Major