Crash in setup_sj_materialization_part1() with semijoin+materialization

[Philip Stoev]

This query:

SELECT * FROM t1
WHERE (a) IN (
        SELECT t5.a
        FROM (
                t2
                LEFT JOIN ( t3 , t4 )
                ON 1 = 1
        )
        JOIN t5
);

crashes as follows:

#3 <signal handler called>
#4 0x0000000000806a2c in setup_sj_materialization_part1 (sjm_tab=0x37bea50) at opt_subselect.cc:2945
#5 0x000000000074a3a4 in make_outerjoin_info (join=0x37afff0) at sql_select.cc:7802
#6 0x000000000074d0d3 in JOIN::optimize (this=0x37afff0) at sql_select.cc:1180
#7 0x00000000007505bb in mysql_select (thd=0x369c108, rref_pointer_array=0x369ece8, tables=0x3722120, wild_num=1, fields=..., conds=0x37a9798, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x37a9a38, unit=0x369e5b0, select_lex=0x369ea98)
    at sql_select.cc:2887
#8 0x0000000000756a7a in handle_select (thd=0x369c108, lex=0x369e510, result=0x37a9a38, setup_tables_done_option=0) at sql_select.cc:283
#9 0x00000000006a33de in execute_sqlcom_select (thd=0x369c108, all_tables=0x3722120) at sql_parse.cc:5090
#10 0x00000000006a50bc in mysql_execute_command (thd=0x369c108) at sql_parse.cc:2234
#11 0x00000000006ade55 in mysql_parse (thd=0x369c108,
    rawbuf=0x3721ea0 "SELECT * FROM t1\nWHERE (a) IN (\nSELECT t5.a\nFROM (\nt2\nLEFT JOIN ( t3 , t4 )\nON 1 = 1\n)\nJOIN t5\n)", length=98,
    found_semicolon=0x40efcf08) at sql_parse.cc:6091
#12 0x00000000006aed25 in dispatch_command (command=COM_QUERY, thd=0x369c108,
    packet=0x37188f9 "SELECT * FROM t1\nWHERE (a) IN (\nSELECT t5.a\nFROM (\nt2\nLEFT JOIN ( t3 , t4 )\nON 1 = 1\n)\nJOIN t5\n)", packet_length=98)
    at sql_parse.cc:1211
#13 0x00000000006b0333 in do_command (thd=0x369c108) at sql_parse.cc:906
#14 0x000000000069ac67 in handle_one_connection (arg=0x369c108) at sql_connect.cc:1186
#15 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#16 0x00000033b58d40cd in clone () from /lib64/libc.so.6

Explain also crashes. The particular number of rows in each table seems to be important.

minimal optimizer switch: semijoin=ON,materialization=ON

full optimizer_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

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-27 00:40:29 +0300
build-date: 2011-08-29 11:23:04 +0300
revno: 3167
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (a int) ;
INSERT IGNORE INTO t1 VALUES (1),(1);

CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (1);

CREATE TABLE t3 (a int);

CREATE TABLE t4 (a int);
INSERT INTO t4 VALUES (2),(2);

CREATE TABLE t5 (a int);
INSERT INTO t5 VALUES (1);

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

SELECT * FROM t1
WHERE (a) IN (
        SELECT t5.a
        FROM (
                t2
                LEFT JOIN ( t3 , t4 )
                ON 1 = 1
        )
        JOIN t5
);