Crash in Item_func::fix_fields on second execution of a prepared statement with semijoin

[Philip Stoev]

The following query:

        SELECT *
        FROM t2
        LEFT JOIN t1 ON t2.a != 0
        AND ( 'j' , 'r' ) IN (
                SELECT b , a
                FROM t3
        );

crashes as follows when executed twice as a prepared statement with semijoin:

#4 <signal handler called>
#5 0x081d838c in Item_ref::fix_fields (this=0xae6138a8, thd=0x9c3c6b8, reference=0xae613a38) at item.cc:6417
#6 0x081f3adb in Item_func::fix_fields (this=0xae6139d8, thd=0x9c3c6b8, ref=0xae613dac) at item_func.cc:180
#7 0x08215327 in Item_cond::fix_fields (this=0xae613cf8, thd=0x9c3c6b8, ref=0x0) at item_cmpfunc.cc:4129
#8 0x08249bee in Item_in_subselect::create_row_in_to_exists_cond (this=0xae62aa80, join=0xae635280,
    where_item=0xae63a4a8, having_item=0xae63a4ac) at item_subselect.cc:2141
#9 0x08249e13 in Item_in_subselect::create_in_to_exists_cond (this=0xae62aa80, join_arg=0xae635280)
    at item_subselect.cc:2197
#10 0x083e3ea6 in JOIN::choose_subquery_plan (this=0xae635280, join_tables=0) at opt_subselect.cc:4462
#11 0x0832364a in make_join_statistics (join=0xae635280, tables_list=..., conds=0x0, keyuse_array=0xae63a3c0)
    at sql_select.cc:3544
#12 0x0831b23c in JOIN::optimize (this=0xae635280) at sql_select.cc:1112
#13 0x081b63c2 in st_select_lex::optimize_unflattened_subqueries (this=0xae62911c) at sql_lex.cc:3126
#14 0x083e3bcf in JOIN::optimize_unflattened_subqueries (this=0xae62cdf0) at opt_subselect.cc:4337
#15 0x0831ce0e in JOIN::optimize (this=0xae62cdf0) at sql_select.cc:1621
#16 0x08321015 in mysql_select (thd=0x9c3c6b8, rref_pointer_array=0xae629258, tables=0xae629950, wild_num=0,
    fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192,
    result=0xae62ac88, unit=0xae628e3c, select_lex=0xae62911c) at sql_select.cc:2886
#17 0x08318e6b in handle_select (thd=0x9c3c6b8, lex=0xae628de0, result=0xae62ac88, setup_tables_done_option=0)
    at sql_select.cc:283
#18 0x082b3cb9 in execute_sqlcom_select (thd=0x9c3c6b8, all_tables=0xae629950) at sql_parse.cc:5102
#19 0x082aaaca in mysql_execute_command (thd=0x9c3c6b8) at sql_parse.cc:2247
#20 0x08363658 in Prepared_statement::execute (this=0xae717b10, expanded_query=0xae9e070c, open_cursor=false)
    at sql_prepare.cc:3735
#21 0x08362b73 in Prepared_statement::execute_loop (this=0xae717b10, expanded_query=0xae9e070c, open_cursor=false,
    packet=0x0, packet_end=0x0) at sql_prepare.cc:3416
#22 0x08361442 in mysql_sql_stmt_execute (thd=0x9c3c6b8) at sql_prepare.cc:2641
#23 0x082aaaf3 in mysql_execute_command (thd=0x9c3c6b8) at sql_parse.cc:2256
#24 0x082b62e8 in mysql_parse (thd=0x9c3c6b8, rawbuf=0xae612a10 "EXECUTE st1", length=11, found_semicolon=0xae9e1228)
    at sql_parse.cc:6102
#25 0x082a8710 in dispatch_command (command=COM_QUERY, thd=0x9c3c6b8, packet=0x9c95549 "EXECUTE st1", packet_length=11)
    at sql_parse.cc:1221
#26 0x082a7b6b in do_command (thd=0x9c3c6b8) at sql_parse.cc:916
#27 0x082a4af5 in handle_one_connection (arg=0x9c3c6b8) at sql_connect.cc:1187
#28 0x00821919 in start_thread () from /lib/libpthread.so.0
#29 0x0076acce in clone () from /lib/libc.so.6

minimal optimizer switch: semijoin=on;

full optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_ sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,m aterialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache= off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimi ze_join_buffer_size=off,table_elimination=on

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

revision-id: <email address hidden>
date: 2011-09-10 18:01:27 +0300
build-date: 2011-09-14 09:12:12 +0300
revno: 3183
branch-nick: maria-5.3

test case:

SET SESSION optimizer_switch = 'semijoin=on';

CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int, b int) ;

PREPARE st1 FROM "
        SELECT *
        FROM t2
        LEFT JOIN t1 ON t2.a != 0
        AND ( 'j' , 'r' ) IN (
                SELECT b , a
                FROM t3
        );
";
EXECUTE st1;
EXECUTE st1;

[Sergey Petrunia (sergefp)]

The crash happens here:

  #0 Item_in_subselect::create_row_in_to_exists_cond (...
  #1 0x08268a05 in Item_in_subselect::create_in_to_exists_cond (...
  #2 0x0842218d in JOIN::choose_subquery_plan (...
  #3 0x0836fec0 in make_join_statistics (...
  #4 0x08371701 in JOIN::optimize (...
  #5 0x081b9b11 in st_select_lex::optimize_unflattened_subqueries (...
  #6 0x08423139 in JOIN::optimize_unflattened_subqueries (...
  #7 0x08373355 in JOIN::optimize (...

We execute this code:

        new Item_func_eq(new
                         Item_direct_ref(&select_lex->context,
                                         (*optimizer->get_cache())->
                                         addr(i),
                                         (char *)"<no matter>",
                                         (char *)in_left_expr_name),
                         new
                         Item_direct_ref(&select_lex->context,
                                         select_lex->
                                         ref_pointer_array+i,
                                         (char *)"<no matter>",
                                         (char *)"<list ref>"));

and during fist execution
(gdb) p *((*optimizer->get_cache())->addr(i))
  $89 = (Item_cache_str *) 0xb48c0b8

while during the second:

(gdb) p *((*optimizer->get_cache())->addr(i))
  $93 = (Cannot access memory at address 0x0

[Sergey Petrunia (sergefp)]

It is not clear why the problem happens only with semijoin=on . The subquery is in the ON clause, so it is not converted to semi-join.

[Sergey Petrunia (sergefp)]

The relationship to semi-joins is as follows: currently, semi-join runtime processes IN subqueries in the ON clauses as follows:
 - it considers them to be semi-join conversion candidates (and so, records them in sj_subselects)
 - convert_join_subqueries_to_semijoins() will not convert subqueries from ON expressions, it will fall-back to IN->EXISTS for them.

apparently, there is something wrong that happens on this execution path.

[Philip Stoev (pstoev-askmonty)]

This can also be an assertion:

mysqld: item_subselect.cc:1394: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.

# 2011-09-15T00:02:01 #6 0x00000033b58296e6 in __assert_fail () from /lib64/libc.so.6
# 2011-09-15T00:02:01 #7 0x000000000062c3ba in Item_in_subselect::val_int (this=0x2aaacc033f68) at item_subselect.cc:1394
# 2011-09-15T00:02:01 #8 0x000000000063979c in Item_func_trig_cond::val_int (this=0x2aaacc15ef70) at item_cmpfunc.h:477
# 2011-09-15T00:02:01 #9 0x000000000073694a in sub_select (join=0x2aaacc024fe0, join_tab=0x2aaacc15e850, end_of_records=false) at sql_select.cc:15083
# 2011-09-15T00:02:01 #10 0x000000000072d4a3 in evaluate_join_record (join=0x2aaacc024fe0, join_tab=0x2aaacc15e530, error=0) at sql_select.cc:15297
# 2011-09-15T00:02:01 #11 0x0000000000736a58 in sub_select (join=0x2aaacc024fe0, join_tab=0x2aaacc15e530, end_of_records=false) at sql_select.cc:15100
# 2011-09-15T00:02:01 #12 0x0000000000737c6f in do_select (join=0x2aaacc024fe0, fields=0x2aaacc02b048, table=0x0, procedure=0x0) at sql_select.cc:14763
# 2011-09-15T00:02:01 #13 0x0000000000757d0a in JOIN::exec (this=0x2aaacc024fe0) at sql_select.cc:2679
# 2011-09-15T00:02:01 #14 0x0000000000633cb9 in subselect_single_select_engine::exec (this=0x2aaacc034748) at item_subselect.cc:2967
# 2011-09-15T00:02:01 #15 0x0000000000638bd4 in Item_subselect::exec (this=0x2aaacc1f0b30) at item_subselect.cc:572
# 2011-09-15T00:02:01 #16 0x000000000062a696 in Item_singlerow_subselect::val_str (this=0x2aaacc1f0b30, str=0x2aaacc1f0b48) at item_subselect.cc:1090
# 2011-09-15T00:02:01 #17 0x0000000000755aef in JOIN::exec (this=0x2aaacc0870e0) at sql_select.cc:2147
# 2011-09-15T00:02:01 #18 0x0000000000751c12 in mysql_select (thd=0x2aaabd11fa08, rref_pointer_array=0x2aaacc0e14c8, tables=0x2aaacc1f1508, wild_num=0, fields=...,
# 2011-09-15T00:02:01 conds=0x2aaacc28c620, og_num=12, order=0x2aaacc14a418, group=0x2aaacc149ec0, having=0x0, proc_param=0x0, select_options=2416232961,
# 2011-09-15T00:02:01 result=0x2aaacc14ad98, unit=0x2aaacc0e0d90, select_lex=0x2aaacc0e1278) at sql_select.cc:2900
# 2011-09-15T00:02:01 #19 0x000000000075803c in handle_select (thd=0x2aaabd11fa08, lex=0x2aaacc0e0cf0, result=0x2aaacc14ad98, setup_tables_done_option=0) at sql_select.cc:283
# 2011-09-15T00:02:01 #20 0x00000000006a40ea in execute_sqlcom_select (thd=0x2aaabd11fa08, all_tables=0x2aaacc1f1508) at sql_parse.cc:5102
# 2011-09-15T00:02:01 #21 0x00000000006a61ca in mysql_execute_command (thd=0x2aaabd11fa08) at sql_parse.cc:2247
# 2011-09-15T00:02:01 #22 0x0000000000770e93 in Prepared_statement::execute (this=0x2aaacc220de8, expanded_query=0x4f01dc60, open_cursor=false) at sql_prepare.cc:3735
# 2011-09-15T00:02:01 #23 0x000000000077119e in Prepared_statement::execute_loop (this=0x2aaacc220de8, expanded_query=0x4f01dc60, open_cursor=false, packet=0x0, packet_end=0x0)
# 2011-09-15T00:02:01 at sql_prepare.cc:3416
# 2011-09-15T00:02:01 #24 0x0000000000771457 in mysql_sql_stmt_execute (thd=0x2aaabd11fa08) at sql_prepare.cc:2641
# 2011-09-15T00:02:01 #25 0x00000000006a61f5 in mysql_execute_command (thd=0x2aaabd11fa08) at sql_parse.cc:2256
# 2011-09-15T00:02:01 #26 0x00000000006ae...