LP:952583 - Server crashes in Item_field::fix_after_pullout on INSERT .. SELECT with derived_merge+semijoin, FROM subquery and IN subquery

#4 <signal handler called>
#5 0x082005f4 in Item_field::fix_after_pullout (this=0x94b2fa0, new_parent=0x93f1540, ref=0x94b2f98)
at item.cc:2443
#6 0x0820c2fe in Item_ref::fix_after_pullout (this=0x94b2ee0, new_parent=0x93f1540, refptr=0x94b3ac8)
at item.cc:7376
#7 0x08225b78 in Item_func::fix_after_pullout (this=0x94b3a68, new_parent=0x93f1540, ref=0x94b37e4)
at item_func.cc:257
#8 0x084200cf in convert_subq_to_sj (parent_join=0x9479568, subq_pred=0x9460868) at opt_subselect.cc:1347
#9 0x0841f176 in convert_join_subqueries_to_semijoins (join=0x9479568) at opt_subselect.cc:862
#10 0x08352904 in JOIN::optimize (this=0x9479568) at sql_select.cc:937
#11 0x0835927c in mysql_select (thd=0x93efae0, rref_pointer_array=0x93f167c, tables=0x945fd90, wild_num=1,
fields=..., conds=0x9460868, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=3489942016, result=0x9460bc0, unit=0x93f1260, select_lex=0x93f1540) at sql_select.cc:2938
#12 0x08350f7f in handle_select (thd=0x93efae0, lex=0x93f1204, result=0x9460bc0,
setup_tables_done_option=1073741824) at sql_select.cc:285
#13 0x082e623f in mysql_execute_command (thd=0x93efae0) at sql_parse.cc:3304
#14 0x082ee6c0 in mysql_parse (thd=0x93efae0,
rawbuf=0x945ec38 "INSERT INTO t1 \nSELECT * FROM ( SELECT * FROM t1 ) AS alias \nWHERE a IN ( SELECT b FROM t2 )", length=92, found_semicolon=0xae88b234) at sql_parse.cc:6152
#15 0x082e10a1 in dispatch_command (command=COM_QUERY, thd=0x93efae0,
packet=0x9449361 "INSERT INTO t1 \nSELECT * FROM ( SELECT * FROM t1 ) AS alias \nWHERE a IN ( SELECT b FROM t2 )", packet_length=92) at sql_parse.cc:1228
#16 0x082e054b in do_command (thd=0x93efae0) at sql_parse.cc:923
#17 0x082dd4d1 in handle_one_connection (arg=0x93efae0) at sql_connect.cc:1193
#18 0xb76a0b25 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-12 02:05:03 +0400
revno: 3455

Also reproducible on MariaDB 5.5 (revno 3316).
No crash on mysql-trunk (revno 3706).

Could not reproduce with a vew instead of FROM subquery.
No crash with SELECT without INSERT.

EXPLAIN for SELECT with default optimizer switch (INSERT .. SELECT crashes):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where 1

EXPLAIN for SELECT with minimal optimizer switch, derived_merge=on,semijoin=on (INSERT .. SELECT still crashes):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`b` = `test`.`t1`.`a`)

Minimal optimizer_switch: derived_merge=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:

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

INSERT INTO t1
SELECT * FROM ( SELECT * FROM t1 ) AS alias
WHERE a IN ( SELECT b FROM t2 );

1. End of test case