Crash in create_ref_for_key and derived_with_keys ( 64 bit )

[Philip Stoev]

The following query:

SELECT *
FROM t2
WHERE t2.f10 IN (
        SELECT v4.f10
        FROM t5
        JOIN ( v4 JOIN t6 AS t6 ON ( t6.f10 = v4.f11 ) )
        ON ( t6.f10 = v4.f10 )
        WHERE v4.f10 = t2.f11
);

crashes as follows on 64-bit platforms:

#3 <signal handler called>
#4 0x0000000000750cd1 in create_ref_for_key (join=0x7f1790097050, j=0x7f17900aa9f0, org_keyuse=0x7f17900a9098, used_tables=4611686018427387911)
    at sql_select.cc:7309
#5 0x0000000000750414 in get_best_combination (join=0x7f1790097050) at sql_select.cc:7123
#6 0x0000000000747371 in make_join_statistics (join=0x7f1790097050, tables_list=..., conds=0x7f17900a6628, keyuse_array=0x7f179009d008)
    at sql_select.cc:3548
#7 0x000000000073e67e in JOIN::optimize (this=0x7f1790097050) at sql_select.cc:1112
#8 0x00000000005a2a3a in st_select_lex::optimize_unflattened_subqueries (this=0x2f7cb40) at sql_lex.cc:3126
#9 0x0000000000827a5d in JOIN::optimize_unflattened_subqueries (this=0x7f1790090e30) at opt_subselect.cc:4384
#10 0x000000000073fd89 in JOIN::optimize (this=0x7f1790090e30) at sql_select.cc:1503
#11 0x0000000000744d6f in mysql_select (thd=0x2f7a1b8, rref_pointer_array=0x2f7cd90, tables=0x7f1790019220, wild_num=1, fields=..., conds=0x7f179003ba90,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7f179008ca40, unit=0x2f7c658, select_lex=0x2f7cb40)
    at sql_select.cc:2886
#12 0x000000000073bcbb in handle_select (thd=0x2f7a1b8, lex=0x2f7c5b8, result=0x7f179008ca40, setup_tables_done_option=0) at sql_select.cc:283
#13 0x00000000006c7fa3 in execute_sqlcom_select (thd=0x2f7a1b8, all_tables=0x7f1790019220) at sql_parse.cc:5112
#14 0x00000000006bed02 in mysql_execute_command (thd=0x2f7a1b8) at sql_parse.cc:2250
#15 0x00000000006caad3 in mysql_parse (thd=0x2f7a1b8,
    rawbuf=0x7f1790018f30 "SELECT *\nFROM t2\nWHERE t2.f10 IN (\nSELECT v4.f10\nFROM t5\nJOIN ( v4 JOIN t6 AS t6 ON ( t6.f10 = v4.f11 ) )\nON ( t6.f10 = v4.f10 )\nWHERE v4.f10 = t2.f11\n)", length=152, found_semicolon=0x7f17aaf5cc68) at sql_parse.cc:6112
#16 0x00000000006bc53f in dispatch_command (command=COM_QUERY, thd=0x2f7a1b8,
    packet=0x2ff4ea9 "SELECT *\nFROM t2\nWHERE t2.f10 IN (\nSELECT v4.f10\nFROM t5\nJOIN ( v4 JOIN t6 AS t6 ON ( t6.f10 = v4.f11 ) )\nON ( t6.f10 = v4.f10 )\nWHERE v4.f10 = t2.f11\n)", packet_length=152) at sql_parse.cc:1221
#17 0x00000000006bb8bf in do_command (thd=0x2f7a1b8) at sql_parse.cc:916
#18 0x00000000006b879b in handle_one_connection (arg=0x2f7a1b8) at sql_connect.cc:1191
#19 0x00000035a7207761 in start_thread () from /lib64/libpthread.so.0
#20 0x00000035a6ee098d in clone () from /lib64/libc.so.6

EXPLAIN also crashes.

minimal switch: derived_with_keys=on;

full switch: 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=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

 bzr version-info
revision-id: <email address hidden>
date: 2011-10-13 13:44:50 +0200
build-date: 2011-10-14 12:40:46 +0300
revno: 3233
branch-nick: maria-5.3

test case:

CREATE TABLE t2 ( f1 int NOT NULL , f3 int, f10 varchar(1) , f11 varchar(1) );
INSERT INTO t2 VALUES (19,1,NULL,NULL),(20,5,'r','r');

CREATE TABLE t4 ( f3 int, f10 varchar(1) , f11 varchar(1) );
INSERT INTO t4 VALUES (1,NULL,NULL),(5,'r','r');

CREATE TABLE t5 ( f11 varchar(1) ) ;

CREATE TABLE t6 ( f1 int NOT NULL , f3 int, f10 varchar(1) );

CREATE ALGORITHM=TEMPTABLE VIEW v4 AS SELECT * FROM t4;

SET SESSION optimizer_switch='derived_with_keys=ON';

SELECT *
FROM t2
WHERE t2.f10 IN (
        SELECT v4.f10
        FROM t5
        JOIN ( v4 JOIN t6 AS t6 ON ( t6.f10 = v4.f11 ) )
        ON ( t6.f10 = v4.f10 )
        WHERE v4.f10 = t2.f11
);