Details
Major
Description
Test case
CREATE TABLE t1 (f1 INT); INSERT INTO t1 VALUES (1); CREATE TABLE t2 (f2 INT, KEY(f2)); INSERT INTO t2 VALUES (8),(0); CREATE TABLE t3 (f3 INT); INSERT INTO t3 VALUES (1),(2); CREATE TABLE t4 (f4 INT); INSERT INTO t4 VALUES (0),(5); SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
Stack trace from 5.5 commit 86f46a3da4a6d82cb510dc4c270d46cfd6a8965b
#3 <signal handler called> #4 0x0000000000677d89 in next_depth_first_tab (join=0x7f1f53969d28, tab=0x7f1f53940c10) at 5.5/sql/sql_select.cc:7672 #5 0x0000000000675df8 in JOIN::get_partial_cost_and_fanout (this=0x7f1f53969d28, end_tab_idx=61, filter_map=18446744073709551615, read_time_arg=0x7f1f543b3518, record_count_arg=0x7f1f543b3520) at 5.5/sql/sql_select.cc:6734 #6 0x000000000077bf81 in JOIN::choose_subquery_plan (this=0x7f1f5396a6b8, join_tables=1) at 5.5/sql/opt_subselect.cc:5454 #7 0x000000000066ed4a in make_join_statistics (join=0x7f1f5396a6b8, tables_list=..., conds=0x0, keyuse_array=0x7f1f5396a9b8) at 5.5/sql/sql_select.cc:3826 #8 0x0000000000665bf2 in JOIN::optimize (this=0x7f1f5396a6b8) at 5.5/sql/sql_select.cc:1229 #9 0x0000000000884178 in subselect_single_select_engine::exec (this=0x7f1f53a88d70) at 5.5/sql/item_subselect.cc:3143 #10 0x000000000087d342 in Item_subselect::exec (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:655 #11 0x000000000087d86e in Item_in_subselect::exec (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:828 #12 0x000000000087fc93 in Item_in_subselect::val_bool (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:1654 #13 0x000000000059ebe1 in Item::val_bool_result (this=0x7f1f53a88bd0) at 5.5/sql/item.h:981 #14 0x000000000081c289 in Item_in_optimizer::val_int (this=0x7f1f5396ac68) at 5.5/sql/item_cmpfunc.cc:1747 #15 0x0000000000805914 in Item::save_in_field (this=0x7f1f5396ac68, field=0x7f1f53940f50, no_conversions=true) at 5.5/sql/item.cc:6059 #16 0x00000000006a3dba in store_key_item::copy_inner (this=0x7f1f543b4170) at 5.5/sql/sql_select.h:1662 #17 0x00000000006a39ad in store_key::copy (this=0x7f1f543b4170) at 5.5/sql/sql_select.h:1552 #18 0x00000000006794b1 in create_ref_for_key (join=0x7f1f53969d28, j=0x7f1f539408f0, org_keyuse=0x7f1f53890660, allow_full_scan=true, used_tables=4611686018427387907) at 5.5/sql/sql_select.cc:8145 #19 0x000000000067845b in get_best_combination (join=0x7f1f53969d28) at 5.5/sql/sql_select.cc:7817 #20 0x000000000066edc5 in make_join_statistics (join=0x7f1f53969d28, tables_list=..., conds=0x7f1f53a88db0, keyuse_array=0x7f1f5396a028) at 5.5/sql/sql_select.cc:3832 #21 0x0000000000665bf2 in JOIN::optimize (this=0x7f1f53969d28) at 5.5/sql/sql_select.cc:1229 #22 0x000000000066c45b in mysql_select (thd=0x7f1f54950060, rref_pointer_array=0x7f1f54953cd0, tables=0x7f1f53a872c0, wild_num=1, fields=..., conds=0x7f1f53a88db0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1f53a88fd0, unit=0x7f1f54953380, select_lex=0x7f1f54953a60) at 5.5/sql/sql_select.cc:3080 #23 0x0000000000662fbd in handle_select (thd=0x7f1f54950060, lex=0x7f1f549532d0, result=0x7f1f53a88fd0, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319 #24 0x000000000063c1fc in execute_sqlcom_select (thd=0x7f1f54950060, all_tables=0x7f1f53a872c0) at 5.5/sql/sql_parse.cc:4689 #25 0x00000000006353de in mysql_execute_command (thd=0x7f1f54950060) at 5.5/sql/sql_parse.cc:2234 #26 0x000000000063ece2 in mysql_parse (thd=0x7f1f54950060, rawbuf=0x7f1f53a87078 "SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) )", length=68, parser_state=0x7f1f543b5620) at 5.5/sql/sql_parse.cc:5909 #27 0x0000000000632925 in dispatch_command (command=COM_QUERY, thd=0x7f1f54950060, packet=0x7f1f54a09061 "SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) )", packet_length=68) at 5.5/sql/sql_parse.cc:1079 #28 0x0000000000631ab1 in do_command (thd=0x7f1f54950060) at 5.5/sql/sql_parse.cc:793 #29 0x0000000000734122 in do_handle_one_connection (thd_arg=0x7f1f54950060) at 5.5/sql/sql_connect.cc:1266 #30 0x0000000000733be1 in handle_one_connection (arg=0x7f1f54950060) at 5.5/sql/sql_connect.cc:1181 #31 0x0000000000b6c629 in pfs_spawn_thread (arg=0x7f1f54971fc0) at 5.5/storage/perfschema/pfs.cc:1015 #32 0x00007f1f5ac7cb50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #33 0x00007f1f58f3270d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
EXPLAIN EXTENDED SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 ref f2 f2 5 const 0 0.00 Using where; Using index 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select 1 AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t3`.`f3` AS `f3` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`f2` = <cache>(<in_optimizer>(1,1 in ( <materialize> (select `test`.`t4`.`f4` from `test`.`t4` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`f4`)))))))
The problem appeared in 5.3 tree with the following revision:
revno: 3765 revision-id: igor@askmonty.org-20140221052733-ypg4dpfcmy5l0b3q parent: psergey@askmonty.org-20140219143412-rp3flx1pmhw6zawn committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.3 timestamp: Thu 2014-02-20 21:27:33 -0800 message: After constant row substitution the optimizer should call the method update_used_tables for the the where condition to update cached indicators of constant subexpressions. It should be done before further possible simplification of the where condition. This change caused simplification of the executed where conditions in many test cases.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Actually, create_ref_for_key executes subqueries only if thd->lex->describe==0.
This is tolerable but still bad. MySQL 5.6.23 has the same code.
Within the scope of this bug, we will only fix the crash.
The "query doesn't produce a SHOW EXPLAIN plan until very long" problem will be split off into a separate bug.
Will need to discuss which one is better.
been over 5 months. Both are better than neither.
(Note: it works only for MyISAM tables for some reason).
Analysis:
The crash is caused by this sequence of events:
join optimizer executes for the upper subquery and produces this plan:
When we reach t2, we attempt to make the index lookup value for it. We call create_ref_for_key(), which causes subquery to be optimized/executed.
Subquery optimization eventually calls outer_join->get_partial_cost_and_fanout(), which tries to access the JOIN_TAB beyond t2, and crashes.
Besides that, why does query optimization phase execute the subquery that returns this->is_expensive()=true?