LP:834492 - Crash in fix_semijoin_strategies_for_picked_join_order with nested subqueries and LooseScan=ON

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