MySQL Bugs: #51016: Batched key access gives wrong results for T0 semijoin (T1 join T2)

Bug #51016	Batched key access gives wrong results for T0 semijoin (T1 join T2)
Submitted:	9 Feb 2010 8:57	Modified:	11 Feb 2010 15:33
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:	Any
Assigned to:		CPU Architecture:	Any
Tags:	BKA, join_cache_level, optimizer_switch, semijoin

Description:
revision-id: tor.didriksen@sun.com-20100208113127-g8wvxcpc2dppmxxd

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4);
create table t1 (a int, b int, key(a));
insert into t1 select a,a from t0;
create table t2 (a int, b int, primary key(a));
insert into t2 select * from t1;
update t1 set a=3, b=11 where a=4;
update t2 set b=11 where a=3;
select * from t0 where t0.a in 
(select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b);
drop table t0,t1,t2;

Diff in the output of SELECT between running with defaults (correct results) and running with --optimizer_join_cache_level=X where X>=5:

 a
 0
-1
-2
-3

Goes away with optimizer_switch=semijoin=off or optimizer_join_cache_level<=4.
Bug found by running main.subselect_sj2 with --optimizer_join_cache_level=8.

How to repeat:
see description

Thank you for the report.

Verified as described.

duplicate of BUG#49129

duplicate of BUG#49129