Server crashes in Item_ref::real_item on INSERT .. SELECT with FROM subquery and derived_merge=ON

[Elena Stepanova]

#4 <signal handler called>
#5 0x08214ee2 in Item_ref::real_item (this=0x9486a20) at item.h:2659
#6 0x0837eae0 in count_field_types (select_lex=0x93ee1b4, param=0x94867b0, fields=...,
    reset_with_sum_func=false) at sql_select.cc:19599
#7 0x083525ae in JOIN::prepare_stage2 (this=0x9486678) at sql_select.cc:864
#8 0x0835249b in JOIN::prepare (this=0x9486678, rref_pointer_array=0x93ee2f0,
    tables_init=0x9485d58, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0,
    group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x93ee1b4,
    unit_arg=0x93edec0) at sql_select.cc:838
#9 0x0835920d in mysql_select (thd=0x93ec740, rref_pointer_array=0x93ee2f0,
    tables=0x9485d58, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0,
    having=0x0, proc_param=0x0, select_options=3489942016, result=0x9486600,
    unit=0x93edec0, select_lex=0x93ee1b4) at sql_select.cc:2932
#10 0x08350f2f in handle_select (thd=0x93ec740, lex=0x93ede64, result=0x9486600,
    setup_tables_done_option=1073741824) at sql_select.cc:285
#11 0x082e6057 in mysql_execute_command (thd=0x93ec740) at sql_parse.cc:3304
#12 0x082ee4d8 in mysql_parse (thd=0x93ec740,
    rawbuf=0x9484bc8 "INSERT INTO t1 SELECT * FROM \n( SELECT * FROM t1 ) AS alias",
    length=59, found_semicolon=0xae9c9234) at sql_parse.cc:6152
#13 0x082e0eb9 in dispatch_command (command=COM_QUERY, thd=0x93ec740,
    packet=0x9444311 "INSERT INTO t1 SELECT * FROM \n( SELECT * FROM t1 ) AS alias",
    packet_length=59) at sql_parse.cc:1228
#14 0x082e0363 in do_command (thd=0x93ec740) at sql_parse.cc:923
#15 0x082dd259 in handle_one_connection (arg=0x93ec740) at sql_connect.cc:1218

bzr version-info
revision-id: <email address hidden>
date: 2012-04-07 17:27:00 -0700
build-date: 2012-04-11 15:49:16 +0400
revno: 3500

Notes:
maria/5.5-release revno 3367 and the latest build on 5.5-release from buildbot also crash, but maria-5.5 revno 3364 does not. 5.5-release looks newer, though.
No crash on MySQL trunk revno 3827 or MySQL 5.5 revno 3737.
Could not replace the FROM subquery with a view, no crash.
SELECT without INSERT does not cause the crash.

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

EXPLAIN for the SELECT with the minimal switch:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
INSERT INTO t1 SELECT * FROM
( SELECT * FROM t1 ) AS alias;

Test case:

SET optimizer_switch = 'derived_merge=on';

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

INSERT INTO t1 SELECT * FROM
  ( SELECT * FROM t1 ) AS alias;

# End of test case

[Igor Babaev (igorb-seattle)]

I've checked that this bug was introduced by the patch for bug #968720.
The bug is not reproducible on rev, 3493, but is reproducible on rev. 3494.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The crash is fixed, but the test suite shows yet another bug:
SET optimizer_switch = 'derived_merge=on';
CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES (2,1),(3,2);
select * from t1;
a b
2 1
3 2
INSERT INTO t1 SELECT * FROM
( SELECT * FROM t1 ) AS alias;
select * from t1;
a b
2 1
3 2
3 2
3 2

i.e. it inserts two times (3,2) and no (2,1).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Problem is that select_insert::send_data uses Item_direct_view_ref which points on field of table t1 (not temporary (derived) table).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

TABLE_LIST::change_refs_to_fields called only once.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

and list for update is empty.