|
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
|
);
|
|
LPexportBug836523.xml