Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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
)
);
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Crash in fix_semijoin_strategies_for_picked_join_order with nested subqueries, view
However, having "loosescan=ON" seems to be essential: if I change it to OFF, the crash doesn't happen.
Re: Crash in fix_semijoin_strategies_for_picked_join_order with nested subqueries and LooseScan=ON
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++)
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.
Re: Crash in fix_semijoin_strategies_for_picked_join_order with nested subqueries and LooseScan=ON
Will need to discuss this with Timour.
Launchpad bug id: 834492
Re: Crash in fix_semijoin_strategies_for_picked_join_order with nested subqueries, view
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
)
);