The following query

SELECT MIN(a) FROM t1, t2
WHERE b IN
  ( SELECT c FROM t3 GROUP BY c );

produces NULL while running with materialization=on and semijoin=on, and a numeric result otherwise. The latter is correct.

revno: 3334
revision-id: <email address hidden>

Reproducible on 3315 as well.

Minimal optimizer_switch: none required (materialization=on and 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

EXPLAIN EXTENDED SELECT MIN(a) FROM t1, t2
WHERE b IN
( SELECT c FROM t3 GROUP BY c );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 const 1 100.00
2 MATERIALIZED t3 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select min(1) AS `MIN(a)` from <materialize> (select 2 from `test`.`t3` group by 2) join `test`.`t1` join `test`.`t2` where (`<subquery2>`.`c` = 2)

Explain without materialization or without semijoin (correct result):

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1

Test case:

CREATE TABLE t1 ( a INT, KEY(a) );
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (2);
CREATE TABLE t3 ( c INT );
INSERT INTO t3 VALUES (2);

SELECT MIN(a) FROM t1, t2
WHERE b IN
  ( SELECT c FROM t3 GROUP BY c );

1. End of test case

1. Note: the same result can be achieved using only one table with one column,
2. see below; but the first test case looked more natural and readable to me.

CREATE TABLE t1 ( a INT, KEY(a) );
INSERT INTO t1 VALUES (1);

SELECT MIN(alias1.a) FROM t1 alias1, t1 alias2
WHERE alias2.a IN
  ( SELECT a FROM t1 GROUP BY a );