Comment 2 for bug 791761

Here is an example where the subquery does not return a single null, but instead returns 1 or more rows. So , it is not such an edge case:

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=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,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

minimal optimizer switch: semijoin=off

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
1 PRIMARY alias2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where

test case:

SET SESSION optimizer_switch='semijoin=off';

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

CREATE TABLE t2 ( f1 int , KEY (f1)) ;
INSERT INTO t2 VALUES (0);

SELECT MAX( alias1.f1 ) AS field1
FROM t2 AS alias1 , t2 AS alias2
WHERE ( 3 ) IN ( SELECT f1 FROM t1 )
HAVING field1 IS NOT NULL;