Crash in best_access_path with materialization+semijoin, aggregate function in IN subquery

[Elena Stepanova]

#3 <signal handler called>
#4 0x000000000077fa9c in best_access_path (join=0x1cea1e0, s=0x1d43fd0, remaining_tables=3, idx=0,
    disable_jbuf=true, record_count=1, pos=0x1cee0a8, loose_scan_pos=0x7f5159ac1d80) at sql_select.cc:5307
#5 0x000000000078212b in best_extension_by_limited_search (join=0x1cea1e0, remaining_tables=3, idx=0,
    record_count=1, read_time=0, search_depth=62, prune_level=1) at sql_select.cc:6414
#6 0x00000000007816a7 in greedy_search (join=0x1cea1e0, remaining_tables=3, search_depth=62, prune_level=1)
    at sql_select.cc:6031
#7 0x0000000000780ef2 in choose_plan (join=0x1cea1e0, join_tables=3) at sql_select.cc:5692
#8 0x000000000077b43a in make_join_statistics (join=0x1cea1e0, tables_list=..., conds=0x1d42f08,
    keyuse_array=0x1cf20a0) at sql_select.cc:3577
#9 0x0000000000772ba1 in JOIN::optimize (this=0x1cea1e0) at sql_select.cc:1149
#10 0x000000000077947f in mysql_select (thd=0x1c73038, rref_pointer_array=0x1c75c58, tables=0x1cb3ab8,
    wild_num=1, fields=..., conds=0x1cb5370, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2147764736, result=0x1cb57b8, unit=0x1c75520, select_lex=0x1c75a08) at sql_select.cc:2928
#11 0x000000000077015d in handle_select (thd=0x1c73038, lex=0x1c75480, result=0x1cb57b8,
    setup_tables_done_option=0) at sql_select.cc:284
#12 0x00000000006feab4 in execute_sqlcom_select (thd=0x1c73038, all_tables=0x1cb3ab8) at sql_parse.cc:5151
#13 0x00000000006f5b71 in mysql_execute_command (thd=0x1c73038) at sql_parse.cc:2284
#14 0x000000000070148f in mysql_parse (thd=0x1c73038,
    rawbuf=0x1cb3830 "SELECT * FROM t1 \nWHERE a IN ( SELECT MAX( c ) FROM t2 ) \nAND b = 107 \nAND ( a IS NL OR a = b )", length=98, found_semicolon=0x7f5159ac3c98) at sql_parse.cc:6152
#15 0x00000000006f3320 in dispatch_command (command=COM_QUERY, thd=0x1c73038,
    packet=0x1caa3c9 "SELECT * FROM t1 \nWHERE a IN ( SELECT MAX( c ) FROM t2 ) \nAND b = 107 \nAND ( a IS NL OR a = b )", packet_length=98) at sql_parse.cc:1228
#16 0x00000000006f2654 in do_command (thd=0x1c73038) at sql_parse.cc:923
#17 0x00000000006ef532 in handle_one_connection (arg=0x1c73038) at sql_connect.cc:1193
#18 0x00007f5163261a4f in start_thread () from /lib64/libpthread.so.0

 bzr version-info
revision-id: <email address hidden>
date: 2012-02-22 17:38:24 +0400
build-date: 2012-02-22 23:18:22 +0200
revno: 3439

EXPLAIN also crashes.

Minimal optimizer_switch: materialization=on,semijoin=on
Full optimizer_switch (default): 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=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch='materialization=on,semijoin=on';

CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES
(7,1),(4,2);

CREATE TABLE t2 ( c INT );
INSERT INTO t2 VALUES (4),(6);

SELECT * FROM t1
WHERE a IN ( SELECT MAX( c ) FROM t2 )
  AND b = 107
  AND ( a IS NULL OR a = b );

[Elena Stepanova (elenst)]

Fix released with 5.3.5-ga.