Server crashes in Bitmap<64u>::merge on INSERT .. SELECT executed as PS, with derived_merge=on, NATURAL JOIN, constant table, MyISAM or Aria

[Elena Stepanova]

#5 0x08210bff in Bitmap<64u>::merge (this=0xe0, map2=...)
    at sql_bitmap.h:161
#6 0x0835c044 in add_key_field (join=0x948f680, key_fields=0xae8fd8cc,
    and_level=0, cond=0x94903d0, field=0x9492708, eq_func=true,
    value=0xae8fd7bc, num_values=1, usable_tables=18446744073709551615,
    sargables=0xae8fda90) at sql_select.cc:3956
#7 0x0835d108 in add_key_fields (join=0x948f680, key_fields=0xae8fd8cc,
    and_level=0xae8fd8d0, cond=0x94903d0,
    usable_tables=18446744073709551615, sargables=0xae8fda90)
    at sql_select.cc:4365
#8 0x0835dd0e in update_ref_and_keys (thd=0x93f0728, keyuse=0x948f860,
    join_tab=0x9490540, tables=2, cond=0x94903d0,
    normal_tables=18446744073709551615, select_lex=0x9471764,
    sargables=0xae8fda90) at sql_select.cc:4717
#9 0x0835a085 in make_join_statistics (join=0x948f680, tables_list=...,
    conds=0x94903d0, keyuse_array=0x948f860) at sql_select.cc:3250
#10 0x083532c5 in JOIN::optimize (this=0x948f680) at sql_select.cc:1150
#11 0x08359140 in mysql_select (thd=0x93f0728, rref_pointer_array=0x94718a0,
    tables=0x9472f48, wild_num=0, fields=..., conds=0x0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=3489942016, result=0x948f608, unit=0x9471484,
    select_lex=0x9471764) at sql_select.cc:2938
#12 0x08350e43 in handle_select (thd=0x93f0728, lex=0x9471428,
    result=0x948f608, setup_tables_done_option=1073741824)
    at sql_select.cc:285
#13 0x082e6053 in mysql_execute_command (thd=0x93f0728) at sql_parse.cc:3304
#14 0x083a5da6 in Prepared_statement::execute (this=0x9471118,
    expanded_query=0xae8fe71c, open_cursor=false) at sql_prepare.cc:3732
#15 0x083a52bb in Prepared_statement::execute_loop (this=0x9471118,
    expanded_query=0xae8fe71c, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3413
#16 0x083a3b83 in mysql_sql_stmt_execute (thd=0x93f0728)
    at sql_prepare.cc:2638
#17 0x082e329b in mysql_execute_command (thd=0x93f0728) at sql_parse.cc:2293
#18 0x082ee4d4 in mysql_parse (thd=0x93f0728,
    rawbuf=0x948f4c8 "EXECUTE stmt", length=12, found_semicolon=0xae8ff234)
    at sql_parse.cc:6152
#19 0x082e0eb5 in dispatch_command (command=COM_QUERY, thd=0x93f0728,
    packet=0x94478e9 "EXECUTE stmt", packet_length=12) at sql_parse.cc:1228
#20 0x082e035f in do_command (thd=0x93f0728) at sql_parse.cc:923
#21 0x082dd2e5 in handle_one_connection (arg=0x93f0728)
    at sql_connect.cc:1193
#22 0xb782bb25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-03-29 21:58:40 +0200
build-date: 2012-03-30 03:22:23 +0400
revno: 3482

Notes:
Also crashes on MariaDB 5.5 revno 3353.
No crash on MariaDB 5.2, MySQL trunk.
No crash when t1 has more than 1 row.
No crash if t1 is an InnoDB table.
No crash if FROM subquery is replaced with a view (either MERGE or TEMPTABLE).
No crash if executed as SELECT, without INSERT.
No if executed outside the prepared statement.

Minimal optimizer_switch: derived_merge=on
Full optimizer_switch (default): on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=off

EXPLAIN for the SELECT part with minimal optimizer_switch (derived_merge=on, everything else OFF):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system a NULL NULL NULL 1 100.00
1 SIMPLE alias2 ref a a 5 const 1 100.00Using index
Warnings:
Note 1003 select 1 AS `a` from `test`.`t1` join `test`.`t2` `alias2` where (`test`.`alias2`.`a` = 1)

Test case:

SET optimizer_switch = 'derived_merge=on';

CREATE TABLE t1 ( a INT, INDEX(a) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 ( a INT, INDEX(a) );
INSERT INTO t2 VALUES (1),(2);

EXPLAIN EXTENDED
SELECT a FROM
  ( SELECT a FROM test.t1 ) AS alias1
  NATURAL JOIN t2 AS alias2;

PREPARE stmt FROM "
INSERT INTO t1
  SELECT a FROM
  ( SELECT a FROM test.t1 ) AS alias1
  NATURAL JOIN t2 AS alias2
";

EXECUTE stmt;

# End of test case

[Elena Stepanova (elenst)]

Setting importance to High (rather than Critical) for now due to the combination of PS, NATURAL JOIN, constant table -- looks like an edge case. Please adjust if needed.

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

View pass because report about non-unique tables and so INSERT SELECT uses buffering (should be fixed for views) this is also could be fix for redived tables in case of INSERT SELECT but it will not work for UPDATE/DELETE...

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

It is strange that TABLE_LIST::change_refs_to_fields does not work...

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

During converting from merged to materialized all items listed in used_item list changed from real tables to the temporary table of materialized join. The problem was that natural join matching made only once and on second prepare phase (first is prepare opf PREPARE statement, second is prepare phase of executing the statement) the list lack fields of natural join because it is not made second time.