Crash in create_ref_for_key with semijoin + materialization

[Philip Stoev]

#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

[Sergey Petrunia (sergefp)]

The crash happens because create_ref_for_key() is invoked with a keyuse that does not match the table:

  Breakpoint 17, create_ref_for_key (join=0xb29d690, j=0xb2a8db4, org_keyuse=0xb2abf70, used_tables=4611686018427387907) at sql_select.cc:7264
(gdb) p j->table->alias->Ptr
  $170 = 0xb291230 "t4"
(gdb) p org_keyuse->table->alias->Ptr
  $171 = 0xb28ce58 "t3"

[Sergey Petrunia (sergefp)]

The mismatch between JOIN_TAB and its KEYUSE is created in fix_semijoin_strategies_for_picked_join_order(), here:

    if (pos->sj_strategy == SJ_OPT_MATERIALIZE)
    {
      SJ_MATERIALIZATION_INFO *sjm= s->emb_sj_nest->sj_mat_info;
      sjm->is_used= TRUE;
      sjm->is_sj_scan= FALSE;
      memcpy(pos - sjm->tables + 1, sjm->positions,
             sizeof(POSITION) * sjm->tables);

The mismatch is caused by this scenario:

- "(1) IN (SELECT t4.f3 FROM t4 , t5)" is converted into a semi-join nest
- JOIN::optimize is invoked for '(SELECT t3.f4 FROM t3 WHERE ... )')
  - optimize_semijoin_nests() remembers SJ-Materialization plan for "(SELECT t4.f3 FROM t4 , t5)"
  - join->choose_subquery_plan() is invoked
    - it adds another keyuse elements
    - it re-runs choose_plan() which choses a different query plan.
- fix_semijoin_strategies() is called, it executes the above code that copies over POSITION elements that has "old" keyuse pointers which now refer to the wrong tables.

that fair choice between IN->EXISTS and Materialization modifi

[Sergey Petrunia (sergefp)]

Results of discussion with Timour: Need to save/restore SJM nest query subplans, and re-optimize them in JOIN::reoptimize()