MySQL Bugs: #51122: incremental join buffering + dup weedout semijoin + STRAIGHT

Bug #51122	incremental join buffering + dup weedout semijoin + STRAIGHT_JOIN = wrong result
Submitted:	11 Feb 2010 21:20	Modified:	11 Mar 2010 7:11
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:	Assigned Account	CPU Architecture:	Any
Tags:	join_cache_level, optimizer_switch, semijoin

Description:
I have revision-id:magne.mahre@sun.com-20100211173921-lwmmw6oto3jrkfxg

I run the test in how-to-repeat, it generates correct results.
Then I run it with both options --mysqld=--optimizer_join_cache_level=X --mysqld=--optimizer_switch=materialization=off for X in (2,4,6,8) and I get such diffs compared to the correct results:

 SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
-2
 insert into t3 values(1);

 SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
-2
 1
 drop table t1,t2,t3;

If I add back materialization or pick other join cache level values, or replace STRAIGHT_JOIN with JOIN, it works.
Adding EXPLAIN shows that Duplicate Weedout is used.

How to repeat:
CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);
CREATE TABLE t3 (i INTEGER);

insert into t1 values(2),(1);
insert into t2 values(2),(1);
insert into t3 values(2);

SELECT t3.i FROM t2 JOIN t3;
SELECT t3.i FROM t2 STRAIGHT_JOIN t3;
explain SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);

insert into t3 values(1);
SELECT t3.i FROM t2 JOIN t3;
SELECT t3.i FROM t2 STRAIGHT_JOIN t3;
explain SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);

drop table t1,t2,t3;

This was found by running subselect_sj_jcl6 with optimizer-switch=materialization=off. The test above is a short version of a piece of subselect_sj_jcl6.
Note that in subselect_sj_jcl6.test, the failing queries above are close to such comment:
#        TODO: The RIGHT join query here generates wrong result for rowcount
which points at BUG#50358, so I wonder if those two bugs could be related. But 50358 is about RIGHT JOIN while the present bug is about STRAIGHT_JOIN, so let's not jump to conclusions.

sorry, remove the "explain" in how-to-repeat. Posting again:
CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);
CREATE TABLE t3 (i INTEGER);

insert into t1 values(2),(1);
insert into t2 values(2),(1);
insert into t3 values(2);

SELECT t3.i FROM t2 JOIN t3;
SELECT t3.i FROM t2 STRAIGHT_JOIN t3;
SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);

insert into t3 values(1);
SELECT t3.i FROM t2 JOIN t3;
SELECT t3.i FROM t2 STRAIGHT_JOIN t3;
SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);

drop table t1,t2,t3;

Thank you for the report.

Verified as described.

Duplicate of BUG#51092