Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
The following test case extracted from subselect_sj.test fails with MDEV-83 due to early subquery optimization.
drop table if exists t0, t1, t2, t10;
set @@storage_engine=myisam;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
insert into t1 values (0,0),(1,1),(2,2);
create table t10 (pk int, a int, primary key(pk));
insert into t10 select a,a from t0;
create table t2 as select * from t1;
– Force a very simple query plan
set join_cache_level=0;
set optimizer_switch='semijoin=off';
select *
from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
prepare s1 from
' select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5';
execute s1;
execute s1;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Approved by SergeyP, pushed to mdev-83.
The bug is related to materialization (both with and without sem-join.
Simpler test case:
------------------------------------------
drop table if exists t1, t2, t10;
set @@storage_engine=myisam;
create table t1(a int, b int);
insert into t1 values (5,5),(7,7);
create table t2(c int, d int);
insert into t2 values (5,5),(7,7);
create table t10 (pk int, a int, primary key(pk));
insert into t10 values (5,5), (7,7);
– Force a very simple query plan
set join_cache_level=0;
set optimizer_switch='semijoin=off,materialization=on,subquery_cache=off';
prepare s1 from
'select * from t1 left join t2 on (t2.c = t1.a and t2.c in (select pk from t10))';
execute s1;
execute s1;
deallocate prepare s1;