MySQL Bugs: #51283: incremental join buffering + dup weedout + 2 semijoins = missing row

Bug #51283	incremental join buffering + dup weedout + 2 semijoins = missing row
Submitted:	18 Feb 2010 14:06	Modified:	11 Mar 2010 7:02
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:	Assigned Account	CPU Architecture:	Any
Tags:	join_cache_level, optimizer_switch, semijoin

Description:
revision-id:guilhem@mysql.com-20100212120808-ss7vpthhub2bsfdg
The SELECT posted in how-to-repeat has one missing row when using optimizer_join_cache_level=X for X in 2,4,6,8.

How to repeat:
set optimizer_switch="materialization=off"; # force optimizer to choose 
# duplicate weedout.
set optimizer_join_cache_level=2; # trigger the bug

CREATE TABLE t1(
  id INTEGER
  );
INSERT INTO t1 VALUES(10),(20);
create table t2 select * from t1;
create table t3 select * from t1;

SELECT *
FROM t1
WHERE 1 IN(SELECT 1
             FROM t2
             WHERE 1 IN(SELECT 1
                          FROM t3));
# the above should return 10,20 but returns only 20.

explain extended SELECT *
FROM t1
WHERE 1 IN(SELECT 1
             FROM t2
             WHERE 1 IN(SELECT 1
                          FROM t3));

drop table t1,t2,t3;

This testcase is inspired from the one of BUG#50361 but they are unrelated bugs (50361 happens with optimizer_join_cache_level=1, has excess instead of missing rows, and does not use Duplicate Weedout semijoin strategy).
This bug might have some connection with 51122, but maybe not, because the present one requires two semijoins.

Thank you for the bug report. Verified just as described on Ubuntu 8.04.

Duplicate of BUG#51092