LP:860553 - Crash in create_ref_for_key with semijoin + materialization

#5 0x0832c786 in create_ref_for_key (join=0xae63add8, j=0xae645994, org_keyuse=0xae633ff8, used_tables=4611686018427387907) at sql_select.cc:7307
#6 0x0832c029 in get_best_combination (join=0xae63add8) at sql_select.cc:7122
#7 0x0832448c in make_join_statistics (join=0xae63add8, tables_list=..., conds=0xae6334e8, keyuse_array=0xae63ff18) at sql_select.cc:3548
#8 0x0831c038 in JOIN::optimize (this=0xae63add8) at sql_select.cc:1112
#9 0x081b67f2 in st_select_lex::optimize_unflattened_subqueries (this=0xb336120) at sql_lex.cc:3126
#10 0x083e4a35 in JOIN::optimize_unflattened_subqueries (this=0xae635b50) at opt_subselect.cc:4334
#11 0x0831dc0a in JOIN::optimize (this=0xae635b50) at sql_select.cc:1621
#12 0x08321e11 in mysql_select (thd=0xb3346e0, rref_pointer_array=0xb33625c, tables=0xae6130c0, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0,
group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae632020, unit=0xb335e40, select_lex=0xb336120) at sql_select.cc:2886
#13 0x08319c67 in handle_select (thd=0xb3346e0, lex=0xb335de4, result=0xae632020, setup_tables_done_option=0) at sql_select.cc:283
#14 0x082b4434 in execute_sqlcom_select (thd=0xb3346e0, all_tables=0xae6130c0) at sql_parse.cc:5110
#15 0x082ab1f1 in mysql_execute_command (thd=0xb3346e0) at sql_parse.cc:2250
#16 0x082b6a63 in mysql_parse (thd=0xb3346e0,
rawbuf=0xae612ec0 "SELECT *\nFROM t1\nJOIN t2\nON ( t2.f5 ) IN (\nSELECT t3.f4 \nFROM t3\nWHERE ( 1 ) IN (\nSELECT t4.f3 \nFROM t4 , t5\n)\n)",
length=112, found_semicolon=0xae9e1228) at sql_parse.cc:6110
#17 0x082a8e40 in dispatch_command (command=COM_QUERY, thd=0xb3346e0,
packet=0xb38d321 "SELECT *\nFROM t1\nJOIN t2\nON ( t2.f5 ) IN (\nSELECT t3.f4 \nFROM t3\nWHERE ( 1 ) IN (\nSELECT t4.f3 \nFROM t4 , t5\n)\n)",
packet_length=112) at sql_parse.cc:1221
#18 0x082a829b in do_command (thd=0xb3346e0) at sql_parse.cc:916
#19 0x082a527f in handle_one_connection (arg=0xb3346e0) at sql_connect.cc:1191
#20 0x00821919 in start_thread () from /lib/libpthread.so.0
#21 0x0076acce in clone () from /lib/libc.so.6

minimal switch: semijoin=on,materialization=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=off,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

EXPLAIN crashes

test case:

CREATE TABLE t1 ( f1 int ) ;

CREATE TABLE t2 ( f5 varchar(52) NOT NULL ) ;

CREATE TABLE t3 ( f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1)) ;

CREATE TABLE t4 ( f3 int, KEY (f3));
INSERT INTO t4 VALUES (17),(20);

CREATE TABLE t5 ( f2 int);
INSERT INTO t5 VALUES (0),(0);

SET SESSION optimizer_switch='semijoin=ON,materialization=ON';

SELECT *
FROM t1
JOIN t2
ON ( t2.f5 ) IN (
SELECT t3.f4
FROM t3
WHERE ( 1 ) IN (
SELECT t4.f3
FROM t4 , t5
)
);

revision-id: <email address hidden>
date: 2011-09-26 23:54:00 +0300
build-date: 2011-09-27 16:15:50 +0300
revno: 3196
branch-nick: maria-5.3