Wrong result with semijoin=on, outer join in maria-5.3-subqueries-mwl90

[Philip Stoev]

Repeatable on maria-5.3-mwl90. Not repeatable in maria-5.3 due to a different query plan.

The following query:

SELECT alias2.f1
FROM t2 AS alias1
LEFT JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2 = alias2.f3 )
ON alias3.f2 = alias2.f2
WHERE alias2.f1 IN ( SELECT f1 FROM t2 AS alias4 ) ;

returns no rows when executed with semijoin=on in maria-5.3-subqueries-mwl90, even though there are rows matching the WHERE clause.

Explain in maria-5.3-subqueries-mwl90:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 Start temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
1 PRIMARY alias2 ALL NULL NULL NULL NULL 3 Using where; End temporary
1 PRIMARY alias3 ALL NULL NULL NULL NULL 3 Using where

explain in maria-5.3:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
1 PRIMARY alias2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
1 PRIMARY alias3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY alias4 ALL NULL NULL NULL NULL 2 Using where

 bzr version-info:

revision-id: <email address hidden>
date: 2011-06-28 18:25:02 +0400
build-date: 2011-06-29 09:45:49 +0300
revno: 3066
branch-nick: maria-5.3-subqueries-mwl90

optimizer_switch in effect:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on

test case:

SET SESSION optimizer_switch = 'semijoin=on,materialization=off,firstmatch=off,loosescan=off';

CREATE TABLE t2 ( f1 int) ;
INSERT IGNORE INTO t2 VALUES (6),(8);
CREATE TABLE t1 ( f1 int, f2 int, f3 int) ;
INSERT IGNORE INTO t1 VALUES (8,0,0),(7,0,0),(9,0,0);
SELECT alias2.f1
FROM t2 AS alias1
LEFT JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2 = alias2.f3 )
ON alias3.f2 = alias2.f2
WHERE alias2.f1 IN ( SELECT f1 FROM t2 AS alias4 ) ;

[Philip Stoev (pstoev-askmonty)]

Another example:

CREATE TABLE t1 ( f10 int) ;
INSERT INTO t1 VALUES (0),(0);

CREATE TABLE t2 ( f10 int, f11 varchar(1)) ;
INSERT INTO t2 VALUES (0,'a'),(0,'b');

CREATE TABLE t3 ( f10 int) ;
INSERT INTO t3 VALUES (0),(0),(0),(0),(0);

CREATE TABLE t4 ( f10 varchar(1), f11 int) ;
INSERT INTO t4 VALUES ('a',0),('b',0);

SELECT * FROM t1
LEFT JOIN ( t2 JOIN t3 ON t3.f10 = t2.f10 ) ON t1.f10 = t2.f10
WHERE t2.f10 IN (
        SELECT t4.f11
        FROM t4
        WHERE t4.f10 != t2.f11
);

[Sergey Petrunia (sergefp)]

Neither of examples are repeatable anymore (after MWL#90 has been merged into 5.3 and fixes).
I've added testcases to the testsuite.