Details
Major
Description
Courtesy of naox
Stack traces are from 5.3 revno 3727.
The problem appeared on 5.3 tree with the following revision:
revno: 3660 revision-id: sanja@askmonty.org-20130606203340-2je46s13kqicdr74 message: MDEV-4593: p_s: crash in simplify_joins with delete using subselect from view mysql_derived_merge_for_insert() should not be called for views or derived tables which are not put (directly or via other views) in main SELECT_LEX "join list".
Two test cases below are very similar, but the stack traces are a bit different, I don't want to take any chances for a partial fix, so I'll file both. Please make sure that the patch fixes both cases.
Variation 1
#3 <signal handler called> #4 0x00000000005fee9a in Item_equal::contains (this=0x28bfcb0, field=0x0) at item_cmpfunc.cc:5605 #5 0x00000000005ac5e1 in Item_field::find_item_equal (this=0x2872d60, cond_equal=0x28befa0) at item.cc:4959 #6 0x00000000005ac8a2 in Item_field::equal_fields_propagator (this=0x2872d60, arg=0x28befa0 "\210ȉ\002\217\217\217\217") at item.cc:5070 #7 0x00000000005bc102 in Item::compile (this=0x2872d60, analyzer=&virtual Item::subst_argument_checker(unsigned char**), arg_p=0x7f30267a1500, transformer=&virtual Item::equal_fields_propagator(unsigned char*), arg_t=0x28befa0 "\210ȉ\002\217\217\217\217") at item.h:1034 #8 0x00000000005d32ce in Item_func::compile (this=0x28c12f0, analyzer=&virtual table offset 760, arg_p=0x7f30267a15c8, transformer=&virtual table offset 776, arg_t=0x28befa0 "\210ȉ\002\217\217\217\217") at item_func.cc:396 #9 0x0000000000744eb2 in build_equal_items_for_cond (thd=0x27b1bc8, cond=0x28c12f0, inherited=0x28befa0) at sql_select.cc:11595 #10 0x0000000000744a14 in build_equal_items_for_cond (thd=0x27b1bc8, cond=0x28beeb8, inherited=0x28befa0) at sql_select.cc:11511 #11 0x0000000000744f74 in build_equal_items (join=0x28c1f80, cond=0x28beeb8, inherited=0x0, join_list=0x2871da8, ignore_on_conds=false, cond_equal_ref=0x28c2398) at sql_select.cc:11681 #12 0x000000000074839b in optimize_cond (join=0x28c1f80, conds=0x28beeb8, join_list=0x2871da8, ignore_on_conds=false, cond_value=0x28c2270, cond_equal=0x28c2398) at sql_select.cc:13227 #13 0x00000000007282ee in JOIN::optimize (this=0x28c1f80) at sql_select.cc:1028 #14 0x00000000008b329a in mysql_derived_optimize (thd=0x27b1bc8, lex=0x286f3c8, derived=0x28733d0) at sql_derived.cc:779 #15 0x00000000008b22c4 in mysql_handle_single_derived (lex=0x286f3c8, derived=0x28733d0, phases=4) at sql_derived.cc:185 #16 0x000000000072470b in TABLE_LIST::handle_derived (this=0x28733d0, lex=0x286f3c8, phases=4) at table.cc:5926 #17 0x000000000058971e in st_select_lex::handle_derived (this=0x2870918, lex=0x286f3c8, phases=4) at sql_lex.cc:3207 #18 0x00000000007246ce in TABLE_LIST::handle_derived (this=0x2874098, lex=0x286f3c8, phases=4) at table.cc:5924 #19 0x000000000058971e in st_select_lex::handle_derived (this=0x286f970, lex=0x286f3c8, phases=4) at sql_lex.cc:3207 #20 0x0000000000727d54 in JOIN::optimize (this=0x28bf110) at sql_select.cc:932 #21 0x000000000072f4b7 in mysql_select (thd=0x27b1bc8, rref_pointer_array=0x286fbc8, tables=0x2870500, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x28bf048, unit=0x286f468, select_lex=0x286f970) at sql_select.cc:2995 #22 0x000000000078dfe6 in mysql_multi_update (thd=0x27b1bc8, table_list=0x2870500, fields=0x286fa80, values=0x286fef8, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x286f468, select_lex=0x286f970) at sql_update.cc:1295 #23 0x00000000006ae5e9 in mysql_execute_command (thd=0x27b1bc8) at sql_parse.cc:3200 #24 0x00000000008d2e04 in sp_instr_stmt::exec_core (this=0x28746f8, thd=0x27b1bc8, nextp=0x7f30267a2b78) at sp_head.cc:2976 #25 0x00000000008d2719 in sp_lex_keeper::reset_lex_and_exec_core (this=0x2874738, thd=0x27b1bc8, nextp=0x7f30267a2b78, open_tables=false, instr=0x28746f8) at sp_head.cc:2794 #26 0x00000000008d2bc6 in sp_instr_stmt::execute (this=0x28746f8, thd=0x27b1bc8, nextp=0x7f30267a2b78) at sp_head.cc:2919 #27 0x00000000008ced08 in sp_head::execute (this=0x286ed20, thd=0x27b1bc8) at sp_head.cc:1283 #28 0x00000000008d0911 in sp_head::execute_procedure (this=0x286ed20, thd=0x27b1bc8, args=0x27b4be8) at sp_head.cc:2015 #29 0x00000000006b28a4 in mysql_execute_command (thd=0x27b1bc8) at sql_parse.cc:4500 #30 0x00000000006b760f in mysql_parse (thd=0x27b1bc8, rawbuf=0x2835900 "CALL pr()", length=9, found_semicolon=0x7f30267a3cb8) at sql_parse.cc:6173 #31 0x00000000006a9624 in dispatch_command (command=COM_QUERY, thd=0x27b1bc8, packet=0x282c499 "CALL pr()", packet_length=9) at sql_parse.cc:1243 #32 0x00000000006a8910 in do_command (thd=0x27b1bc8) at sql_parse.cc:923 #33 0x00000000006a5799 in handle_one_connection (arg=0x27b1bc8) at sql_connect.cc:1231 #34 0x00007f302ff92b50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #35 0x00007f302f335a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Test case:
CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,2),(3,4); CREATE TABLE t2 (c INT); INSERT INTO t2 VALUES (5),(6); CREATE TABLE t3 (d INT); INSERT INTO t3 VALUES (7),(8); CREATE PROCEDURE pr() UPDATE t3, (SELECT c FROM (SELECT 1 FROM t1 WHERE a=72 AND b) sq, t2 ) sq2 SET d=sq2.c; CALL pr(); CALL pr();
Variation 2
#3 <signal handler called> #4 0x00000000005bcbe9 in Item_field::result_type (this=0x22eed70) at item.h:1850 #5 0x00000000007441c2 in check_simple_equality (left_item=0x22eed70, right_item=0x233d2f0, item=0x233d380, cond_equal=0x7f093b763700) at sql_select.cc:11213 #6 0x0000000000744718 in check_equality (thd=0x222dbc8, item=0x233d380, cond_equal=0x7f093b763700, eq_list=0x7f093b763750) at sql_select.cc:11374 #7 0x000000000074481b in build_equal_items_for_cond (thd=0x222dbc8, cond=0x233aec0, inherited=0x0) at sql_select.cc:11476 #8 0x0000000000744f74 in build_equal_items (join=0x233e070, cond=0x233aec0, inherited=0x0, join_list=0x22eddb8, ignore_on_conds=false, cond_equal_ref=0x233e488) at sql_select.cc:11681 #9 0x000000000074839b in optimize_cond (join=0x233e070, conds=0x233aec0, join_list=0x22eddb8, ignore_on_conds=false, cond_value=0x233e360, cond_equal=0x233e488) at sql_select.cc:13227 #10 0x00000000007282ee in JOIN::optimize (this=0x233e070) at sql_select.cc:1028 #11 0x00000000008b329a in mysql_derived_optimize (thd=0x222dbc8, lex=0x22eb3e8, derived=0x22ef458) at sql_derived.cc:779 #12 0x00000000008b22c4 in mysql_handle_single_derived (lex=0x22eb3e8, derived=0x22ef458, phases=4) at sql_derived.cc:185 #13 0x000000000072470b in TABLE_LIST::handle_derived (this=0x22ef458, lex=0x22eb3e8, phases=4) at table.cc:5926 #14 0x000000000058971e in st_select_lex::handle_derived (this=0x22ec938, lex=0x22eb3e8, phases=4) at sql_lex.cc:3207 #15 0x00000000007246ce in TABLE_LIST::handle_derived (this=0x22f0168, lex=0x22eb3e8, phases=4) at table.cc:5924 #16 0x000000000058971e in st_select_lex::handle_derived (this=0x22eb990, lex=0x22eb3e8, phases=4) at sql_lex.cc:3207 #17 0x0000000000727d54 in JOIN::optimize (this=0x233b118) at sql_select.cc:932 #18 0x000000000072f4b7 in mysql_select (thd=0x222dbc8, rref_pointer_array=0x22ebbe8, tables=0x22ec520, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x233b050, unit=0x22eb488, select_lex=0x22eb990) at sql_select.cc:2995 #19 0x000000000078dfe6 in mysql_multi_update (thd=0x222dbc8, table_list=0x22ec520, fields=0x22ebaa0, values=0x22ebf18, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x22eb488, select_lex=0x22eb990) at sql_update.cc:1295 #20 0x00000000006ae5e9 in mysql_execute_command (thd=0x222dbc8) at sql_parse.cc:3200 #21 0x00000000008d2e04 in sp_instr_stmt::exec_core (this=0x22f07c8, thd=0x222dbc8, nextp=0x7f093b764b78) at sp_head.cc:2976 #22 0x00000000008d2719 in sp_lex_keeper::reset_lex_and_exec_core (this=0x22f0808, thd=0x222dbc8, nextp=0x7f093b764b78, open_tables=false, instr=0x22f07c8) at sp_head.cc:2794 #23 0x00000000008d2bc6 in sp_instr_stmt::execute (this=0x22f07c8, thd=0x222dbc8, nextp=0x7f093b764b78) at sp_head.cc:2919 #24 0x00000000008ced08 in sp_head::execute (this=0x22ead30, thd=0x222dbc8) at sp_head.cc:1283 #25 0x00000000008d0911 in sp_head::execute_procedure (this=0x22ead30, thd=0x222dbc8, args=0x2230be8) at sp_head.cc:2015 #26 0x00000000006b28a4 in mysql_execute_command (thd=0x222dbc8) at sql_parse.cc:4500 #27 0x00000000006b760f in mysql_parse (thd=0x222dbc8, rawbuf=0x22b1900 "CALL pr()", length=9, found_semicolon=0x7f093b765cb8) at sql_parse.cc:6173 #28 0x00000000006a9624 in dispatch_command (command=COM_QUERY, thd=0x222dbc8, packet=0x22a8499 "CALL pr()", packet_length=9) at sql_parse.cc:1243 #29 0x00000000006a8910 in do_command (thd=0x222dbc8) at sql_parse.cc:923 #30 0x00000000006a5799 in handle_one_connection (arg=0x222dbc8) at sql_connect.cc:1231 #31 0x00007f0944f54b50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #32 0x00007f09442f7a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Test case:
CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,2),(3,4); CREATE TABLE t2 (c INT); INSERT INTO t2 VALUES (5),(6); CREATE TABLE t3 (d INT); INSERT INTO t3 VALUES (7),(8); CREATE PROCEDURE pr() UPDATE t3, (SELECT c FROM (SELECT 1 FROM t1 WHERE a=72 AND NOT b) sq, t2 ) sq2 SET d=sq2.c; CALL pr(); CALL pr();
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
If to change the WHERE condition "a=72 AND b" / "a=72 AND NOT b" for an equivalent "a=72 AND b<>0" / "a=72 AND b=0"
everything works fine.
THD::thd->activate_stmt_arena_if_needed() should be used to temporary activating statement arena instead of direct usage of THD::set_n_backup_active_arena() because possible such scenario:
1) func1 saves current arena and activates copy1 of statement arena
2) func2 saves copy1 of statement arena setup by func1 and activates copy2
3) some changes made for copy 2
4) func2 stores changed copy2 back to statenet arena and activates copy1
5) func1 store unchanged copy1 back to statemnt arena (rewrite changed copy 2 so changes become lost) and activates arena which was before.
committed for review
The same problem present in 5.1 for sure, but I have no test suite. So it should be decided where if we will fix it.
ok to push in 5.1
merged up to 5.3, waits of merge in upper branches...
The problem is that Item_field of 'b' field is not called during prepare on second execution ('a' fix field called despite the fact they are in the same WHERE condition).