Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization

[Philip Stoev]

The following query:

SELECT t1.a
FROM t1
WHERE t1.a IN (
        SELECT t5.a
        FROM t5, t6
)
HAVING a IN (
        SELECT a
        FROM t7
);

crashes as follows:

#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

It appears it is important for table t5 to have zero rows.

explain:

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

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:50:40 +0300
revno: 3167
branch-nick: maria-5.3

test case:

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
);

[Sergey Petrunia (sergefp)]

The crash happens inside JOIN::get_partial_cost_and_fanout() in this code:

      sj_inner_fanout= 1.0;
      last_sj_table= i + tab->n_sj_tables;
    }

>> if (tab->records_read && (tab->table->map & filter_map))
    {
      record_count *= tab->records_read;
      read_time += tab->read_time;
      if (tab->emb_sj_nest)

we crash because tab->table==NULL.

tab->table==NULL because 'tab' refers to SJ-Materialization nes