Bug #53172 Semijoin materialization-scan gives wrong results for certain query plans
Submitted: 26 Apr 2010 17:29 Modified: 28 Apr 2010 13:08
Reporter: Guilhem Bichot Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0-codebase-bugfixing OS:Linux
Assigned to: CPU Architecture:Any
Tags: materialization, optimizer_switch

[26 Apr 2010 17:29] Guilhem Bichot
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;
[26 Apr 2010 17:32] Guilhem Bichot
patch with debug-specific keywords

Attachment: patch.txt (text/plain), 2.24 KiB.

[26 Apr 2010 18:37] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 Apr 2010 13:08] Øystein Grøvlen
This is a duplicate of Bug#52068.  I have verified that by applying the two fixes that are mentioned in that bug report (Bug#45714 + Resetting of read cursor on subsequent scans), the example query in this report will give the correct result