Not reproducible with maria-5.3 due to different query plan. Reproducible with derived_merge=off, derived_with_keys=off .

The following query:

SELECT *
FROM t1 NATURAL LEFT JOIN ( t2 , t3 )
WHERE t2.f3 IN ( SELECT * FROM v4 );

returns no rows with semijoin in maria-5.3-subqueries-mwl89, even though other execution plans and server versions agree that rows should be returned.

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where
3 DERIVED t4 index NULL f2 5 NULL 2 Using index

minimal optimizer switch required: semijoin=on,firstmatch=off

total 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=off,derived_with_keys=off,firstmatch=off,loosescan=on,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,firstmatch=off';

CREATE TABLE t1 (f1 int, f2 int );
INSERT INTO t1 VALUES (2,0),(4,0),(0,NULL);

CREATE TABLE t2 (f2 int, f3 int );
INSERT INTO t2 VALUES (NULL,NULL),(0,0);

CREATE TABLE t3 ( f1 int, f3 int );
INSERT INTO t3 VALUES (2,0),(4,0),(0,NULL),(4,0),(8,0);

CREATE TABLE t4 ( f2 int, KEY (f2) );
INSERT INTO t4 VALUES (0),(NULL);

CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ;

SELECT *
FROM t1 NATURAL LEFT JOIN ( t2 , t3 )
WHERE t2.f3 IN ( SELECT * FROM v4 );

bzr version-info:

revision-id: <email address hidden>
date: 2011-06-29 15:07:28 +0400
build-date: 2011-06-29 16:46:52 +0300
revno: 3070
branch-nick: maria-5.3-subqueries-mwl90