LP:952372 - Server crashes on 2nd execution of PS in find_field_in_tables with semijoin+materialization, DISTINCT, view, aggregate function in IN subquery

See also https://bugs.launchpad.net/maria/+bug/952297, they might be related, both came from the same original query.

#4 <signal handler called>
#5 0x0833b05e in find_field_in_tables (thd=0x93efae0, item=0x94806c0, first_table=0x94801f0, last_table=0x0,
ref=0x9477f88, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true)
at sql_base.cc:6581
#6 0x0820554a in Item_field::fix_fields (this=0x94806c0, thd=0x93efae0, reference=0x9477f88) at item.cc:4585
#7 0x0823f684 in Item_in_optimizer::fix_left (this=0x9477f28, thd=0x93efae0, ref=0xae8d8388)
at item_cmpfunc.cc:1437
#8 0x0823face in Item_in_optimizer::fix_fields (this=0x9477f28, thd=0x93efae0, ref=0xae8d8388)
at item_cmpfunc.cc:1485
#9 0x0833dce2 in setup_tables (thd=0x93efae0, context=0x94775f8, from_clause=0x9477688, tables=0x94813d0,
leaves=..., select_insert=false, full_table_list=false) at sql_base.cc:8029
#10 0x0833de77 in setup_tables_and_check_access (thd=0x93efae0, context=0x94775f8, from_clause=0x9477688,
tables=0x94813d0, leaves=..., select_insert=false, want_access_first=1, want_access=1,
full_table_list=false) at sql_base.cc:8083
#11 0x083516ca in JOIN::prepare (this=0x94c6818, rref_pointer_array=0x9477700, tables_init=0x94813d0,
wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0,
proc_param_init=0x0, select_lex_arg=0x94775c4, unit_arg=0x94772e4) at sql_select.cc:591
#12 0x084b185d in st_select_lex_unit::prepare (this=0x94772e4, thd_arg=0x93efae0, sel_result=0x9478680,
additional_options=0) at sql_union.cc:325
#13 0x084b4701 in mysql_derived_prepare (thd=0x93efae0, lex=0x947f698, derived=0x94801f0)
at sql_derived.cc:625
#14 0x084b3e32 in mysql_handle_single_derived (lex=0x947f698, derived=0x94801f0, phases=2)
at sql_derived.cc:176
#15 0x0834fa76 in TABLE_LIST::handle_derived (this=0x94801f0, lex=0x947f698, phases=2) at table.cc:5829
#16 0x081e7368 in st_select_lex::handle_derived (this=0x947f9d4, lex=0x947f698, phases=2) at sql_lex.cc:3170
#17 0x083515f3 in JOIN::prepare (this=0x94b9c28, rref_pointer_array=0x947fb10, tables_init=0x94801f0,
wild_num=0, conds_init=0x94785f0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0,
proc_param_init=0x0, select_lex_arg=0x947f9d4, unit_arg=0x947f6f4) at sql_select.cc:578
#18 0x0835925d in mysql_select (thd=0x93efae0, rref_pointer_array=0x947fb10, tables=0x94801f0, wild_num=0,
fields=..., conds=0x94785f0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2416200193, result=0x9481158, unit=0x947f6f4, select_lex=0x947f9d4) at sql_select.cc:2932
#19 0x08350f7f in handle_select (thd=0x93efae0, lex=0x947f698, result=0x9481158, setup_tables_done_option=0)
at sql_select.cc:285
#20 0x082ec085 in execute_sqlcom_select (thd=0x93efae0, all_tables=0x94801f0) at sql_parse.cc:5151
#21 0x082e3461 in mysql_execute_command (thd=0x93efae0) at sql_parse.cc:2284
#22 0x083a5bb6 in Prepared_statement::execute (this=0x947f388, expanded_query=0xae8d971c, open_cursor=false)
at sql_prepare.cc:3732
#23 0x083a50cb in Prepared_statement::execute_loop (this=0x947f388, expanded_query=0xae8d971c,
open_cursor=false, packet=0x0, packet_end=0x0) at sql_prepare.cc:3413
#24 0x083a3993 in mysql_sql_stmt_execute (thd=0x93efae0) at sql_prepare.cc:2638
#25 0x082e3487 in mysql_execute_command (thd=0x93efae0) at sql_parse.cc:2293
#26 0x082ee6c0 in mysql_parse (thd=0x93efae0, rawbuf=0x945ec10 "EXECUTE pstmt", length=13,
found_semicolon=0xae8da234) at sql_parse.cc:6152
#27 0x082e10a1 in dispatch_command (command=COM_QUERY, thd=0x93efae0, packet=0x9449351 "EXECUTE pstmt",
packet_length=13) at sql_parse.cc:1228
#28 0x082e054b in do_command (thd=0x93efae0) at sql_parse.cc:923
#29 0x082dd4d1 in handle_one_connection (arg=0x93efae0) at sql_connect.cc:1193
#30 0xb76efb25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-03-05 22:33:46 -0800
build-date: 2012-03-11 20:50:07 +0400
revno: 3455

Also reproducible on MariaDB 5.5 (revno 3316).
Not reproducible on MySQL 5.6 (trunk revno 3706).

EXPLAIN:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 Using temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Distinct; Using join buffer (incremental, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select distinct `test`.`t1`.`a` AS `a` from <materialize> (select min(`test`.`t2`.`b`) from `test`.`t2`) join `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `<subquery2>`.`MIN(b)`)

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 );
INSERT INTO t1 VALUES (2),(3);
CREATE VIEW v1 AS SELECT * FROM t1;

CREATE TABLE t2 ( b VARCHAR(1) );
INSERT INTO t2 VALUES ('v'),('v');

PREPARE pstmt FROM
'SELECT DISTINCT a FROM v1, t2
WHERE b IN ( SELECT MIN(b) FROM t2 )';

EXECUTE pstmt;
EXECUTE pstmt;