LP:836523 - Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization

SELECT t1.a

FROM t1

WHERE t1.a IN (

        SELECT t5.a

        FROM t5, t6

)

HAVING a IN (

        SELECT a

        FROM t7

);

#3  <signal handler called>

#4  0x000000000071d9ed in JOIN::get_partial_cost_and_fanout (this=0xb7da790, end_tab_idx=2, filter_map=18446744073709551615, read_time_arg=0x41bd6c50,

    record_count_arg=0x41bd6c58) at sql_select.cc:6117

#5  0x00000000008029b0 in JOIN::choose_subquery_plan (this=0xb7e6bd0, join_tables=1) at opt_subselect.cc:4512

#6  0x000000000074a253 in make_join_statistics (join=0xb7e6bd0, tables_list=..., conds=0x0, keyuse_array=0xb7ecb88) at sql_select.cc:3545

#7  0x000000000074ca82 in JOIN::optimize (this=0xb7e6bd0) at sql_select.cc:1113

#8  0x000000000057ab4e in st_select_lex::optimize_unflattened_subqueries (this=0xb6d0c58) at sql_lex.cc:3126

#9  0x00000000008038c0 in JOIN::optimize_unflattened_subqueries (this=0xb7da790) at opt_subselect.cc:4318

#10 0x000000000074eac1 in JOIN::optimize (this=0xb7da790) at sql_select.cc:1622

#11 0x00000000007505bb in mysql_select (thd=0xb6ce2c8, rref_pointer_array=0xb6d0ea8, tables=0xb752eb8, wild_num=0, fields=..., conds=0xb754550, og_num=0,

    order=0x0, group=0x0, having=0xb78e5c0, proc_param=0x0, select_options=2147764736, result=0xb78e810, unit=0xb6d0770, select_lex=0xb6d0c58)

    at sql_select.cc:2887

#12 0x0000000000756a7a in handle_select (thd=0xb6ce2c8, lex=0xb6d06d0, result=0xb78e810, setup_tables_done_option=0) at sql_select.cc:283

#13 0x00000000006a33de in execute_sqlcom_select (thd=0xb6ce2c8, all_tables=0xb752eb8) at sql_parse.cc:5090

#14 0x00000000006a50bc in mysql_execute_command (thd=0xb6ce2c8) at sql_parse.cc:2234

#15 0x00000000006ade55 in mysql_parse (thd=0xb6ce2c8,

    rawbuf=0xb752c30 "SELECT t1.a\nFROM t1\nWHERE t1.a IN (\nSELECT t5.a\nFROM t5, t6\n)\nHAVING a IN (\nSELECT a\nFROM t7\n)", length=94,

    found_semicolon=0x41bd8f08) at sql_parse.cc:6091

#16 0x00000000006aed25 in dispatch_command (command=COM_QUERY, thd=0xb6ce2c8,

    packet=0xb7497c9 "SELECT t1.a\nFROM t1\nWHERE t1.a IN (\nSELECT t5.a\nFROM t5, t6\n)\nHAVING a IN (\nSELECT a\nFROM t7\n)", packet_length=94)

    at sql_parse.cc:1211

#17 0x00000000006b0333 in do_command (thd=0xb6ce2c8) at sql_parse.cc:906

#18 0x000000000069ac67 in handle_one_connection (arg=0xb6ce2c8) at sql_connect.cc:1186

#19 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0

#20 0x00000033b58d40cd in clone () from /lib64/libc.so.6

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra

1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables

3       SUBQUERY        t7      ALL     NULL    NULL    NULL    NULL    2

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

revision-id: <email address hidden>

date: 2011-08-27 00:40:29 +0300

build-date: 2011-08-29 11:50:40 +0300

revno: 3167

branch-nick: maria-5.3

CREATE TABLE t1 (a varchar(1));

INSERT INTO t1 VALUES ('a'),('a');

CREATE TABLE t5 (a varchar(1));

CREATE TABLE t6 (a int);

INSERT INTO t6 VALUES (1),(2);

CREATE TABLE t7 (a varchar(1));

INSERT INTO t7 VALUES ('a'),('a');

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

SELECT t1.a

FROM t1

WHERE t1.a IN (

        SELECT t5.a

        FROM t5, t6

)

HAVING a IN (

        SELECT a

        FROM t7

);