Crash in check_simple_equality() with semijoin + materialization + prepared statements

[Philip Stoev]

backtrace:

#3 <signal handler called>
#4 0x0000000000747ad3 in check_simple_equality (left_item=0x18a1b3a8, right_item=0x18a1b4b8, item=0x18a1b5b8, cond_equal=0x40e84fb0) at sql_select.cc:10459
#5 0x0000000000748515 in check_equality (thd=0x1890b1e8, item=0x18a1b5b8, cond_equal=0x40e84fb0, eq_list=0x40e85050) at sql_select.cc:10715
#6 0x000000000074860d in build_equal_items_for_cond (thd=0x1890b1e8, cond=0x18990e20, inherited=0x0) at sql_select.cc:10817
#7 0x0000000000748d85 in build_equal_items (thd=0x1890b1e8, cond=0x18990e20, inherited=0x0, join_list=0x18a1b298, cond_equal_ref=0x18a1af48)
    at sql_select.cc:11014
#8 0x0000000000748f4b in build_equal_items (thd=0x1890b1e8, cond=0x0, inherited=0x0, join_list=0x18a191c8, cond_equal_ref=0x18a29b88) at sql_select.cc:11051
#9 0x00000000007494cb in optimize_cond (join=0x18a23aa0, conds=0x0, join_list=0x18a191c8, cond_value=0x18a29a70, cond_equal=0x18a29b88)
    at sql_select.cc:12436
#10 0x000000000074e91e in JOIN::optimize (this=0x18a23aa0) at sql_select.cc:975
#11 0x0000000000639260 in Item_in_subselect::optimize (this=0x18a1bcd0, out_rows=0x40e855c0, cost=0x40e855b8) at item_subselect.cc:613
#12 0x000000000074e104 in inject_jtbm_conds (join=0x18a1d880, join_list=0x18a17f48, join_where=0x18a23940) at sql_select.cc:817
#13 0x000000000074e8d4 in JOIN::optimize (this=0x18a1d880) at sql_select.cc:973
#14 0x0000000000752cd1 in mysql_select (thd=0x1890b1e8, rref_pointer_array=0x18a18030, tables=0x18a18bd8, wild_num=0, fields=..., conds=0x18a2d030,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200194, result=0x18a1b7c0, unit=0x18a178f8, select_lex=0x18a17de0)
    at sql_select.cc:2886
#15 0x00000000007591a0 in handle_select (thd=0x1890b1e8, lex=0x18a17858, result=0x18a1b7c0, setup_tables_done_option=0) at sql_select.cc:283
#16 0x00000000006a4dcc in execute_sqlcom_select (thd=0x1890b1e8, all_tables=0x18a18bd8) at sql_parse.cc:5110
#17 0x00000000006a6eac in mysql_execute_command (thd=0x1890b1e8) at sql_parse.cc:2250
#18 0x0000000000771fbb in Prepared_statement::execute (this=0x189b8a18, expanded_query=0x40e86c60, open_cursor=false) at sql_prepare.cc:3735
#19 0x00000000007722c6 in Prepared_statement::execute_loop (this=0x189b8a18, expanded_query=0x40e86c60, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3416
#20 0x000000000077257f in mysql_sql_stmt_execute (thd=0x1890b1e8) at sql_prepare.cc:2641
#21 0x00000000006a6ed7 in mysql_execute_command (thd=0x1890b1e8) at sql_parse.cc:2259
#22 0x00000000006afc97 in mysql_parse (thd=0x1890b1e8, rawbuf=0x18990ca0 "EXECUTE st1", length=11, found_semicolon=0x40e87f08) at sql_parse.cc:6110
#23 0x00000000006b0825 in dispatch_command (command=COM_QUERY, thd=0x1890b1e8, packet=0x18987929 "EXECUTE st1", packet_length=11) at sql_parse.cc:1221
#24 0x00000000006b1e4f in do_command (thd=0x1890b1e8) at sql_parse.cc:916
#25 0x000000000069c2e3 in handle_one_connection (arg=0x1890b1e8) at sql_connect.cc:1191
#26 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#27 0x00000033b58d40cd in clone () from /lib64/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1
1 PRIMARY t1 ALL NULL NULL NULL NULL 0 Using join buffer (flat, BNL join)
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table

test case:

CREATE TABLE t1 ( a int );

CREATE TABLE t3 ( b int, c int) ;

CREATE TABLE t2 ( a int ) ;

CREATE TABLE t4 ( a int , c int) ;

SET SESSION optimizer_switch='semijoin=ON,materialization=ON';

PREPARE st1 FROM "
SELECT STRAIGHT_JOIN *
FROM t1
WHERE ( 3 ) IN (
        SELECT t3.b
        FROM t3
        LEFT JOIN (
                t2 STRAIGHT_JOIN t4 ON ( t4.c = t2.a )
        ) ON ( t4.a = t3.c )
);
";
EXECUTE st1;
EXECUTE st1;

 bzr version-info
revision-id: <email address hidden>
date: 2011-10-06 01:21:15 +0400
build-date: 2011-10-06 08:58:27 +0300
revno: 3213
branch-nick: maria-5.3

[Sergey Petrunia (sergefp)]

The crash is caused by the following execution scenario:

PREPARE command:
> parent subquery calls convert_join_subqueries_to_semi_joins()
   - it switches to statement' Arena. (AA)
   - subquery is converted into a non-mergeable semi-join nest,
  > convert_subq_to_jtbm()
     > it calls child_join->optimize()
       (this is necessary because of how JOIN::choose_subquery_plan() works.
        in particular, it may figure out that Materialization is not applicable after all
        and this information needs to be returned to convert_subq_to_jtbm() so that
        it aborts the conversion)
        > child_join->optimize() works:
           - switches to statement arena *AGAIN* (BB)
           - it calls simplify_joins(), which converts the outer join into inner.
           - while doing the above, it creates an Item_cond_and() object. The object is added into
              thd->free_list.

From this point, the execution proceeds normally, except that double activation (at (AA) and (BB) ) and de-activation of statement mem_root leaves the Item_cond_and un-recorded in Statement's free_list.
As a consequence of this, Item_cond_and->cleanup() is never called, and on next execution we get situation
where

Item_cond_and->fixed=TRUE
Item_cond_and->list->nth_element(0)->fixed= FALSE
Item_cond_and->list->nth_element(1)->fixed= FALSE

this causes us to assert when accessing one of Item_cond_and's chlldren.