Crash in fix_semijoin_strategies_for_picked_join_order with nested subqueries and LooseScan=ON

[Philip Stoev]

The following query:

SELECT *
FROM v3
WHERE v3.b > ALL (
        SELECT c
        FROM t4
        WHERE t4.a >= v3.b
        AND a = SOME (
                SELECT b
                FROM t5
        )
);

causes the following backtrace:

#4 <signal handler called>
#5 0x083df730 in fix_semijoin_strategies_for_picked_join_order (join=0xae53a508) at opt_subselect.cc:2878
#6 0x08329c00 in get_best_combination (join=0xae53a508) at sql_select.cc:7007
#7 0x08322566 in make_join_statistics (join=0xae53a508, tables_list=..., conds=0xae52dd88, keyuse_array=0xae53f648) at sql_select.cc:3542
#8 0x0831a151 in JOIN::optimize (this=0xae53a508) at sql_select.cc:1113
#9 0x081b5e0c in st_select_lex::optimize_unflattened_subqueries (this=0x9f060fc) at sql_lex.cc:3126
#10 0x083e2867 in JOIN::optimize_unflattened_subqueries (this=0xae52dfa0) at opt_subselect.cc:4318
#11 0x0831b734 in JOIN::optimize (this=0xae52dfa0) at sql_select.cc:1504
#12 0x0831ff2b in mysql_select (thd=0x9f046b8, rref_pointer_array=0x9f06238, tables=0xae5127e8, wild_num=1, fields=..., conds=0xae5140d0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae5144e8, unit=0x9f05e1c, select_lex=0x9f060fc)
    at sql_select.cc:2887
#13 0x08317da3 in handle_select (thd=0x9f046b8, lex=0x9f05dc0, result=0xae5144e8, setup_tables_done_option=0) at sql_select.cc:283
#14 0x082b3171 in execute_sqlcom_select (thd=0x9f046b8, all_tables=0xae5127e8) at sql_parse.cc:5090
#15 0x082a9f75 in mysql_execute_command (thd=0x9f046b8) at sql_parse.cc:2234
#16 0x082b57ad in mysql_parse (thd=0x9f046b8,
    rawbuf=0xae5125f0 "SELECT * \nFROM v3\nWHERE v3.b > ALL (\nSELECT c\nFROM t4\nWHERE t4.a >= v3.b \nAND a = SOME (\nSELECT b\nFROM t5\n)\n)", length=109,
    found_semicolon=0xae8e1228) at sql_parse.cc:6091
#17 0x082a7bf2 in dispatch_command (command=COM_QUERY, thd=0x9f046b8,
    packet=0x9f5d549 "SELECT * \nFROM v3\nWHERE v3.b > ALL (\nSELECT c\nFROM t4\nWHERE t4.a >= v3.b \nAND a = SOME (\nSELECT b\nFROM t5\n)\n)",
    packet_length=109) at sql_parse.cc:1211
#18 0x082a704d in do_command (thd=0x9f046b8) at sql_parse.cc:906
#19 0x082a40b5 in handle_one_connection (arg=0x9f046b8) at sql_connect.cc:1186
#20 0x00821919 in start_thread () from /lib/libpthread.so.0
#21 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t5 index c c 10 NULL 2 Using where; Using index; Start temporary
2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using where; End temporary

minimal optimizer switch:

semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF

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-23 15:51:47 +0300
build-date: 2011-08-26 11:02:59 +0300
revno: 3166
branch-nick: maria-5.3

test case:

CREATE TABLE t3 (b int) ;
INSERT INTO t3 VALUES (0),(0);
CREATE OR REPLACE VIEW v3 AS SELECT * FROM t3;

CREATE TABLE t4 (a int, b int, c int, d int, PRIMARY KEY (a)) ;
INSERT INTO t4 VALUES (28,0,0,0),(29,3,0,0);

CREATE TABLE t5 (a int, b int, c int, d int, KEY (c,b)) ;
INSERT INTO t5 VALUES (28,0,0,0),(29,3,0,0);

SET SESSION optimizer_switch='semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF';

SELECT *
FROM v3
WHERE v3.b > ALL (
        SELECT c
        FROM t4
        WHERE t4.a >= v3.b
        AND a = SOME (
                SELECT b
                FROM t5
        )
);

[Sergey Petrunia (sergefp)]

The problem is repeatable without use of VIEWs: here's a simplified testcase that crashes in the same way:

CREATE TABLE t3 (b int) ;
INSERT INTO t3 VALUES (0),(0);

CREATE TABLE t4 (a int, b int, c int, d int, PRIMARY KEY (a)) ;
INSERT INTO t4 VALUES (28,0,0,0),(29,3,0,0);

CREATE TABLE t5 (a int, b int, c int, d int, KEY (c,b)) ;
INSERT INTO t5 VALUES (28,0,0,0),(29,3,0,0);

SET SESSION optimizer_switch='semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF';

SELECT *
FROM t3
WHERE t3.b > ALL (
        SELECT c
        FROM t4
        WHERE t4.a >= t3.b
        AND a = SOME (
                SELECT b
                FROM t5
        )
);

[Sergey Petrunia (sergefp)]

However, having "loosescan=ON" seems to be essential: if I change it to OFF, the crash doesn't happen.

[Sergey Petrunia (sergefp)]

The crash happens when fix_semijoin_strategies_for_picked_join_order is called
for this subquery:

         SELECT c
         FROM t4
         WHERE t4.a >= t3.b
         AND a = SOME (
                 SELECT b
                 FROM t5
         )

it is a result of the following scenario:

- subquery "(SELECT b FROM t5)" is converted into a semi-join and merged into
  its parent
- join optimization is started for "t4 semi join t5"
- we pick a join order of (t5,t3), with LooseScan strategy on table t5.
- execution hits these lines in JOIN::choose_subquery_plan:

      /*
        The subquery was not reoptimized either because the user allowed only
        the IN-EXISTS strategy, or because materialization was not possible
        based on semantic analysis. Cleanup the original plan and reoptimize.
      */
      for (uint i= 0; i < table_count; i++)
      {
        join_tab[i].keyuse= NULL;
        join_tab[i].checked_keys.clear_all();
      }

  These lines destroy the KEYUSE element that was used to create LooseScan
  access to table t5. Subsequent call to reoptimize() doesn't add the keyuse
  back.

- Then execution proceeds to fix_semijoin_strategies_for_picked_join_order()
  where it crashes when it attempts to run best_access_path() on table t5 to
  get the LooseScan scan, and doesn't get it.

[Sergey Petrunia (sergefp)]

Will need to discuss this with Timour.