Bug #57623 subquery within before insert trigger causes crash (semijoin=on)
Submitted: 21 Oct 2010 10:43 Modified: 2 Mar 2011 3:16
Reporter: SaiKumar V Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:mysql-next-mr-opt-backporting OS:Any
Assigned to: Roy Lyseng CPU Architecture:Any
Tags: optimizer_switch, semijoin

[21 Oct 2010 10:43] SaiKumar V
Description:
Insert into a table containing before insert trigger causes server crash when semijoin=on.

INSERT INTO trigger18270 VALUES (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query

set session optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

INSERT INTO trigger18270 VALUES (1);
Query OK, 1 row affected (0.04 sec)

How to repeat:
Bug found in:-
mysql-next-mr-opt-backporting
revision-id: tor.didriksen@oracle.com-20100930150822-jipdt0g5uh7zhx5k

This a RQG test failure, Optimizer settings used for RQG are as below:
SET GLOBAL OPTIMIZER_SWITCH = 'semijoin=on';

Attaching the testcase, as its larger than 8k in size.
[21 Oct 2010 10:44] SaiKumar V
semijoin trigger crash

Attachment: semijoin_crash.txt (text/plain), 10.88 KiB.

[21 Oct 2010 13:26] SaiKumar V
Trace from Crash:-

key_buffer_size=1048576
read_buffer_size=131072
max_used_connections=3
max_threads=151
thread_count=2
connection_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 60163 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xaf11110
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xa8c7b388 thread_stack 0x30000
(my_print_stacktrace+0x32)[0x8633ae3]
(handle_segfault+0x2d1)[0x815356b]
[0x194420]
(_ZN4JOIN18flatten_subqueriesEv+0x602)[0x82067a6]
(_ZN4JOIN8optimizeEv+0xab)[0x82154d3]
(_ZN30subselect_single_select_engine4execEv+0xbb)[0x8385e1d]
(_ZN14Item_subselect4execEv+0x8c)[0x8386826]
(_ZN21Item_exists_subselect8val_boolEv+0x49)[0x8383163]
(_ZN12Item_cond_or7val_intEv+0x65)[0x8337631]
(_ZN4Item8val_boolEv+0x50)[0x831cf54]
(_ZN13Item_cond_and7val_intEv+0x65)[0x83376fb]
[0x81f72c5]
(_Z10sub_selectP4JOINP13st_join_tableb+0x149)[0x81f7a5d]
[0x82046bb]
(_ZN4JOIN4execEv+0xa2b)[0x821d7ed]
(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x30d)[0x8219829]
(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1e4)[0x821f1f4]
(_Z21mysql_execute_commandP3THD+0x2a73)[0x81cac1f]
(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x11)[0x83d845f]
(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x17b)[0x83d8dfb]
(_ZN13sp_instr_stmt7executeEP3THDPj+0x1c2)[0x83d9596]
(_ZN7sp_head7executeEP3THD+0x51b)[0x83db91b]
(_ZN7sp_head15execute_triggerEP3THDPK19st_mysql_lex_stringS4_P13st_grant_info+0x316)[0x83dd5ea]
(_ZN19Table_triggers_list16process_triggersEP3THD14trg_event_type20trg_action_time_typeb+0x16f)[0x82520c5]
(_Z36fill_record_n_invoke_before_triggersP3THDPP5FieldR4ListI4ItemEbP19Table_triggers_list14trg_event_type+0x60)[0x817c438]
(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0xa03)[0x81b8175]
(_Z21mysql_execute_commandP3THD+0x27a4)[0x81ca950]
(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e2)[0x81cff2e]
(_Z16dispatch_command19enum_server_commandP3THDPcj+0x905)[0x81d1261]
(_Z10do_commandP3THD+0x242)[0x81d24be]
(_Z24do_handle_one_connectionP3THD+0x18c)[0x8293042]
(handle_one_connection+0x25)[0x8293125]
/lib/libpthread.so.0[0x81443b]
/lib/libc.so.6(clone+0x5e)[0x76bfde]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xb04ee90 = INSERT INTO transforms.trigger2994   SELECT    ( ( table1 . `col_int_nokey` ) * ( table2 . `pk` ) ) AS field1 FROM ( C AS table1 INNER JOIN CC AS table2 ON (table2 . `pk` = table1 . `col_int_key`  ) ) WHERE (   EXISTS ( SELECT DISTINCT  SUBQUERY1_t1 . `col_int_key` AS SUBQUERY1_field1 FROM ( BB AS SUBQUERY1_t1 STRAIGHT_JOIN ( C AS SUBQUERY1_t2 INNER JOIN B AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `col_int_nokey`  ) ) ON (SUBQUERY1_t3 . `col_int_nokey` = SUBQUERY1_t2 . `pk`  AND ( SUBQUERY1_t1 . `col_varchar_nokey` , SUBQUERY1_t1 . `col_varchar_nokey` )  IN ( SELECT   CHILD_SUBQUERY1_t1 . `col_varchar_nokey` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t1 . `col_varchar_key` AS CHILD_SUBQUERY1_field2 FROM ( view_C AS CHILD_SUBQUERY1_t1 RIGHT OUTER JOIN ( ( C AS CHILD_SUBQUERY1_t2 LEFT  JOIN C AS CHILD_SUBQUERY1_t3 ON (CHILD_SUBQUERY1_t3 . `col_varchar_key` = CHILD_SUBQUERY1_t2 . `col_varchar_nokey` ) ) ) ON (CHILD_SUBQUERY1_t3 . `pk` = CHILD_SUBQUERY1_t2 . `pk` ) ) WHERE ( CHILD_SUBQUERY1_t1 . `
thd->thread_id=7
thd->killed=NOT_KILLED
[27 Oct 2010 13:49] Roy Lyseng
Preliminary analysis shows that this problem has the same root cause as one of the problems described in WL5561, the problem described in the section titled "Assert failure in backout_nj_sj_state()".

The problem is that changes to "prep_on_expr" are not applied permanently.

Hence, it seems that the problem is related to INNER JOIN specifications, as well as OUTER JOINs.
[13 Jan 2011 14:21] Roy Lyseng
The problem seems to be of a slightly more general nature: It may happen for every subquery for which semijoin transformation is rejected, prepared (non-convential) mode is active, and the subquery predicate is part of a join condition attached to a join nest.
[13 Jan 2011 14:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128664

3323 Roy Lyseng	2011-01-13
      Bug#57623: subquery within before insert trigger causes crash (semijoin=on)
      
      Crash when attempting to transform a subquery using IN_TO_EXISTS inside
      JOIN::flatten_subqueries(), when semijoin transformation is impossible
      for the subquery, and in prepared (non-conventional) mode.
      
      The problem is that replace_subcondition() is attempted with prep_on_expr as
      "tree" argument, but prep_on_expr has not yet been set. prep_on_expr
      is set for prepared statements in fix_prepare_info_in_table_list(), but only
      for table objects, not for join nest objects. In this case, prep_on_expr
      is not set before the subsequently called simplify_joins() function, which
      will propagate the desired information.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#57623.
      
      mysql-test/r/subquery_sj_all.result
        Added test results for bug#58561.
      mysql-test/r/subquery_sj_all_jcl6.result
        Added test results for bug#58561.
      mysql-test/r/subquery_sj_all_jcl7.result
        I think you are starting to get the picture by now...
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      
      sql/sql_select.cc
        Added test on *tree being non-NULL before calling replace_subcondition()
        in JOIN::flatten_subqueries().
[24 Jan 2011 16:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129466

3329 Roy Lyseng	2011-01-24
      Bug#57623: subquery within before insert trigger causes crash (semijoin=on)
      
      Crash when attempting to transform a subquery using IN_TO_EXISTS inside
      JOIN::flatten_subqueries(), when semijoin transformation is impossible
      for the subquery, and in prepared (non-conventional) mode.
      
      The problem is that replace_subcondition() is attempted with prep_on_expr as
      "tree" argument, but prep_on_expr has not yet been set. prep_on_expr
      is set for prepared statements in fix_prepare_info_in_table_list(), but only
      for table objects, not for join nest objects. In this case, prep_on_expr
      is not set before the subsequently called simplify_joins() function, which
      will propagate the desired information.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#57623.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Added test results for bug#57623.
      
      sql/sql_select.cc
        Added test on *tree being non-NULL before calling replace_subcondition()
        in JOIN::flatten_subqueries().
[25 Jan 2011 7:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129508

3329 Roy Lyseng	2011-01-25
      Bug#57623: subquery within before insert trigger causes crash (semijoin=on)
      
      Crash when attempting to transform a subquery using IN_TO_EXISTS inside
      JOIN::flatten_subqueries(), when semijoin transformation is impossible
      for the subquery, and in prepared (non-conventional) mode.
      
      The problem is that replace_subcondition() is attempted with prep_on_expr as
      "tree" argument, but prep_on_expr has not yet been set. prep_on_expr
      is set for prepared statements in fix_prepare_info_in_table_list(), but only
      for table objects, not for join nest objects. In this case, prep_on_expr
      is not set before the subsequently called simplify_joins() function, which
      will propagate the desired information.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#57623.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Added test results for bug#57623.
      
      sql/sql_select.cc
        Added test on *tree being non-NULL before calling replace_subcondition()
        in JOIN::flatten_subqueries().
[27 Jan 2011 11:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129743

3334 Roy Lyseng	2011-01-27
      Bug#57623: subquery within before insert trigger causes crash (semijoin=on)
      
      Crash when attempting to transform a subquery using IN_TO_EXISTS inside
      JOIN::flatten_subqueries(), when semijoin transformation is impossible
      for the subquery, and in prepared (non-conventional) mode.
      
      The problem is that replace_subcondition() is attempted with prep_on_expr as
      "tree" argument, but prep_on_expr has not yet been set. prep_on_expr
      is set for prepared statements in fix_prepare_info_in_table_list(), but only
      for table objects, not for join nest objects. In this case, prep_on_expr
      is not set before the subsequently called simplify_joins() function, which
      will propagate the desired information.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#57623.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Added test results for bug#57623.
      
      sql/sql_select.cc
        Added test on *tree being non-NULL before calling replace_subcondition()
        in JOIN::flatten_subqueries().
        Added DBUG_ASSERT to validate that the assumption for this fix is true.
[2 Feb 2011 13:24] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:jorgen.loland@oracle.com-20110202132358-khrjqzdcs3jrda3i) (version source revid:jorgen.loland@oracle.com-20110202132358-khrjqzdcs3jrda3i) (merge vers: 5.6.2) (pib:24)
[2 Mar 2011 3:16] Paul DuBois
Noted in 5.6.2 changelog.

With semi-join optimization enabled, the server crashed when
attempting to transform a subquery using IN_TO_EXISTS inside
JOIN::flatten_subqueries(), when semi-join transformation is
impossible for the subquery, and in prepared mode.

CHANGESET - http://lists.mysql.com/commits/129743
[8 Apr 2011 14:53] Paul DuBois
Correction. Bug does not affect 5.6.x users. No changelog entry needed.