Server hangs on INSERT .. SELECT with derived_merge, FROM subquery, UNION

[Elena Stepanova]

The following query

INSERT INTO t1 SELECT * FROM ( SELECT * FROM t1 ) AS alias UNION SELECT * FROM t2

makes server hang, seemingly forever, if it's run with derived_merge=on (current default).

Reproducible on current maria/5.3 (revno 3521) and maria/5.5 (revno 3402).
On 5.3 it started happening with revno 3421 (works fine on 3420, hangs on 3421).

SELECT without INSERT does not hang.

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

Stack trace while the query is hanging:

#0 0x0000000000724f5a in unique_table (thd=0x1cd4948, table=0x1d59280, table_list=0x1d5a598, check_alias=false) at sql_base.cc:1711
#1 0x000000000079007f in select_insert::prepare (this=0x1d8d550, values=..., u=0x1cd6e30) at sql_insert.cc:3173
#2 0x000000000074bb93 in JOIN::prepare (this=0x1d95d40, rref_pointer_array=0x1d8c930, tables_init=0x1cd6e80, 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=0x1d8c6e0, unit_arg=0x1cd6e30) at sql_select.cc:834
#3 0x0000000000753600 in mysql_select (thd=0x1cd4948, rref_pointer_array=0x1d8c930, tables=0x1cd6e80, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0x1d8d550, unit=0x1cd6e30, select_lex=0x1d8c6e0) at sql_select.cc:2918
#4 0x00000000008da6da in st_select_lex_unit::exec (this=0x1cd6e30) at sql_union.cc:755
#5 0x00000000008d7f64 in mysql_union (thd=0x1cd4948, lex=0x1cd6d90, result=0x1d8d550, unit=0x1cd6e30, setup_tables_done_option=1073741824) at sql_union.cc:35
#6 0x000000000074a1a8 in handle_select (thd=0x1cd4948, lex=0x1cd6d90, result=0x1d8d550, setup_tables_done_option=1073741824) at sql_select.cc:263
#7 0x00000000006d0e0a in mysql_execute_command (thd=0x1cd4948) at sql_parse.cc:3304
#8 0x00000000006d9b05 in mysql_parse (thd=0x1cd4948, rawbuf=0x1d59130 "INSERT INTO t1 SELECT * FROM ( SELECT * FROM t1 ) AS alias UNION SELECT * FROM t2", length=81, found_semicolon=0x7f8d7a5107e8) at sql_parse.cc:6152
#9 0x00000000006cb5d5 in dispatch_command (command=COM_QUERY, thd=0x1cd4948, packet=0x1d4fcc9 "INSERT INTO t1 SELECT * FROM ( SELECT * FROM t1 ) AS alias UNION SELECT * FROM t2", packet_length=81) at sql_parse.cc:1228
#10 0x00000000006ca8bc in do_command (thd=0x1cd4948) at sql_parse.cc:923
#11 0x00000000006c7754 in handle_one_connection (arg=0x1cd4948) at sql_connect.cc:1218
#12 0x00007f8d83d19efc in start_thread (arg=0x7f8d7a511700) at pthread_create.c:304
#13 0x00007f8d832c159d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

# Test case:

SET optimizer_switch='derived_merge=on';

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 UNION SELECT * FROM t2;

# End of test case