|
Note: I could only reproduce it on 10.0, because the test case requires use_stat_tables, otherwise it doesn't crash. I suppose the crash has nothing to do with the statistics, it just makes server to choose the faulty plan. I cannot however positively verify it, because EXPLAIN also crashes. In any case, please see if the underlying issue exists in 5.3/5.5, and if so, modify Fix version accordingly.
#3 <signal handler called>
|
#4 0x0000000000733f3f in test_if_skip_sort_order (tab=0x7f371c08cb10, order=0x7f371c02b650, select_limit=1, no_changes=false, map=0x7f371c029558) at /home/elenst/bzr/10.0/sql/sql_select.cc:19334
|
#5 0x00000000006ecf15 in JOIN::optimize_inner (this=0x7f371c02c248) at /home/elenst/bzr/10.0/sql/sql_select.cc:1786
|
#6 0x00000000006e876a in JOIN::optimize (this=0x7f371c02c248) at /home/elenst/bzr/10.0/sql/sql_select.cc:1003
|
#7 0x0000000000a7d274 in Item_in_subselect::optimize (this=0x7f371c02b690, out_rows=0x7f374468f540, cost=0x7f374468f548) at /home/elenst/bzr/10.0/sql/item_subselect.cc:704
|
#8 0x00000000008be7ae in setup_jtbm_semi_joins (join=0x7f371c02b970, join_list=0x59690f0, join_where=0x7f371c02bd88) at /home/elenst/bzr/10.0/sql/opt_subselect.cc:5165
|
#9 0x00000000006e9319 in JOIN::optimize_inner (this=0x7f371c02b970) at /home/elenst/bzr/10.0/sql/sql_select.cc:1158
|
#10 0x00000000006e876a in JOIN::optimize (this=0x7f371c02b970) at /home/elenst/bzr/10.0/sql/sql_select.cc:1003
|
#11 0x00000000006f49d9 in mysql_select (thd=0x5965d80, rref_pointer_array=0x59691e0, tables=0x7f371c021a90, wild_num=1, fields=..., conds=0x7f371c02b690, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f371c02b950, unit=0x59688a8, select_lex=0x5968f80) at /home/elenst/bzr/10.0/sql/sql_select.cc:3215
|
#12 0x00000000006e5ba0 in handle_select (thd=0x5965d80, lex=0x59687f0, result=0x7f371c02b950, setup_tables_done_option=0) at /home/elenst/bzr/10.0/sql/sql_select.cc:371
|
#13 0x00000000006a14d3 in execute_sqlcom_select (thd=0x5965d80, all_tables=0x7f371c021a90) at /home/elenst/bzr/10.0/sql/sql_parse.cc:5021
|
#14 0x0000000000694909 in mysql_execute_command (thd=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_parse.cc:2482
|
#15 0x00000000006a55ca in mysql_parse (thd=0x5965d80, rawbuf=0x7f371c0216f8 "SELECT * FROM t1 WHERE ( a, a ) IN ( \nSELECT b, MIN( c ) FROM t2, t3 WHERE ( b, c ) IN ( \nSELECT t1_inner2.a, t1_inner1.a FROM t1 AS t1_inner1, t1 AS t1_inner2 \nWHERE t1_inner2.a = t1_inner1.a AND t1_"..., length=234, parser_state=0x7f3744690510) at /home/elenst/bzr/10.0/sql/sql_parse.cc:6154
|
#16 0x000000000068fde5 in dispatch_command (command=COM_QUERY, thd=0x5965d80, packet=0x5969fd1 "SELECT * FROM t1 WHERE ( a, a ) IN ( \nSELECT b, MIN( c ) FROM t2, t3 WHERE ( b, c ) IN ( \nSELECT t1_inner2.a, t1_inner1.a FROM t1 AS t1_inner1, t1 AS t1_inner2 \nWHERE t1_inner2.a = t1_inner1.a AND t1_"..., packet_length=234) at /home/elenst/bzr/10.0/sql/sql_parse.cc:1274
|
#17 0x000000000068ea82 in do_command (thd=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_parse.cc:983
|
#18 0x00000000008499a9 in do_handle_one_connection (thd_arg=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_connect.cc:1267
|
#19 0x00000000008494c4 in handle_one_connection (arg=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_connect.cc:1181
|
#20 0x0000000000c64604 in pfs_spawn_thread (arg=0x5948110) at /home/elenst/bzr/10.0/storage/perfschema/pfs.cc:1800
|
#21 0x00007f37521d3e9a in start_thread (arg=0x7f3744691700) at pthread_create.c:308
|
#22 0x00007f37512b1cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
bzr version-info
revision-id: wlad@montyprogram.com-20130523125248-22m56m01r24t0dmb
|
revno: 3752
|
branch-nick: 10.0
|
Minimal optimizer_switch: materialization=on,semijoin=on
Default optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
|
EXPLAIN also crashes.
Test case:
--source include/have_innodb.inc
|
|
SET optimizer_switch = 'materialization=on,semijoin=on';
|
SET use_stat_tables=PREFERABLY;
|
|
CREATE TABLE t1 (a VARCHAR(1) NOT NULL, INDEX (a)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES ('x');
|
|
CREATE TABLE t2 (b VARCHAR(1), INDEX (b)) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES ('z');
|
|
CREATE TABLE t3 (c VARCHAR(1), INDEX (c)) ENGINE=InnoDB;
|
INSERT INTO t3 VALUES ('y'),('y');
|
|
ANALYZE TABLE t1, t2, t3;
|
|
SELECT * FROM t1 WHERE ( a, a ) IN (
|
SELECT b, MIN( c ) FROM t2, t3 WHERE ( b, c ) IN (
|
SELECT t1_inner2.a, t1_inner1.a FROM t1 AS t1_inner1, t1 AS t1_inner2
|
WHERE t1_inner2.a = t1_inner1.a AND t1_inner2.a != 's'
|
)
|
GROUP BY b
|
);
|
|