The following query

SELECT * FROM t1
    RIGHT JOIN t2 ON a = b
  WHERE b IN ( SELECT a FROM t1 )

when is run with materialization=ON and semijoin=ON (current defaults), returns NULLs instead of actual values for table t1, even although there is a match for the ON condition. When either materialization or semijoin is OFF, the result is correct.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-08 04:22:38 +0400
build-date: 2011-12-08 21:55:58 +0300
revno: 3337
branch-nick: maria-5.3

EXPLAIN output with semijoin=on, materialization=on (wrong result):

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00
select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t1`) left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`b`)) where 1

EXPLAIN output with semijoin=on, materialization=off (correct result):

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t1`) left join `test`.`t1` on(((`test`.`t1`.`a` = `test`.`t2`.`b`) and (`test`.`t1`.`a` = `test`.`t2`.`b`))) where (`test`.`t1`.`a` = `test`.`t2`.`b`)

EXPLAIN output with semijoin=off, materialization=on (correct result):

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00
select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`b`)) where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,`test`.`t2`.`b` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`b` in <temporary table> on distinct_key where ((`test`.`t2`.`b` = `<subquery2>`.`a`))))))

Minimal optimizer_switch: not required (materialization=on, semijoin=on by default)

Full optimizer_switch: 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=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,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=off,table_elimination=on

Test case:

CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (11),(12);
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (11),(12);

SELECT * FROM t1
    RIGHT JOIN t2 ON a = b
  WHERE b IN ( SELECT a FROM t1 );

1. End of test case

1. Correct (expected) result:
2. a b
3. 11 11
4. 12 12

1. Real result:
2. a b
3. NULL 11
4. NULL 12