Description:
I have alfranio.correia@sun.com-20100426092226-bnkzpjs8uqkl7l16 .
I patch it with the attached file, so that I can force the optimizer to choose a predefined order of tables for the query execution plan, and a certain semijoin strategy. This allows me to test situations which are hard to provoke otherwise. So even though I am patching the server, this situation is expected to happen in real life in unpatched servers, so is a real bug.
This bug may or not be related to BUG#52068.
I run the test in "how-to-repeat"; the execution plan is:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ta_ot ALL NULL NULL NULL NULL 4
1 PRIMARY tb_it ALL NULL NULL NULL NULL 4 Materialize; Scan
1 PRIMARY tc_ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer
which is expected, but the SELECT returns:
a a
1 1
1 1
1 1
1 1
where the correct result would be:
a a
1 1
1 1
2 2
2 2
1 1
1 1
2 2
2 2
Disabling join buffering doesn't help.
Bug goes away with materialization=off.
How to repeat:
Patch with the attached patch and run this:
# This is to test how sjm-scan handles plan "ot, it, ot2"
create table ta_ot(a int);
create table tb_it(a int);
create table tc_ot2(a int);
insert into ta_ot values(1),(2),(1),(2);
insert into tc_ot2 values(1),(2),(1),(2);
insert into tb_it values(1),(2),(1),(2);
set optimizer_prune_level=0;
set optimizer_switch="semijoin=on,firstmatch=off,materialization=on,loosescan=off";
set debug="+d,info,query,opt,optimizer_force_alphab_sorted_plan,optimizer_avoid_dups_weedout";
explain select * from ta_ot,tc_ot2
where (ta_ot.a,tc_ot2.a) in (select a,a from tb_it);
select * from ta_ot,tc_ot2
where (ta_ot.a,tc_ot2.a) in (select a,a from tb_it);
drop table ta_ot,tb_it,tc_ot2;
