The following query

SELECT alias1.* FROM
( SELECT * FROM t1 ) AS alias1
RIGHT JOIN t2
ON ( b = a )
WHERE
t2.b IN ( SELECT c FROM t3 );

Returns NULLs instead of numeric column values with derived_merge=ON, and correct values with derived_merge=OFF.

EXPLAIN with derived_merge=ON:

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 func 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00

select `test`.`t1`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t2`.`b`)) where 1

EXPLAIN with derived_merge=OFF:

1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 func 1 100.00
1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2100.00 Using where
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00

select `alias1`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) left join (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `alias1` on(((`alias1`.`a` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where 1

Minimal optimizer_switch: none needed (derived_merge=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 (4),(1);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (4),(1);
CREATE TABLE t3 (c INT);
INSERT INTO t3 VALUES (4),(1);

SELECT alias1.* FROM
( SELECT * FROM t1 ) AS alias1
RIGHT JOIN t2
ON ( b = a )
WHERE
t2.b IN ( SELECT c FROM t3 );

bzr version-info:

revision-id: <email address hidden>
date: 2011-12-06 13:42:18 -0800
build-date: 2011-12-07 20:37:21 +0300
revno: 3334
branch-nick: maria-5.3