Server crashes on 2nd execution of PS in find_field_in_tables with semijoin+materialization, DISTINCT, view, aggregate function in IN subquery

[Elena Stepanova]

See also https://bugs.launchpad.net/maria/+bug/952297, they might be related, both came from the same original query.

#4 <signal handler called>
#5 0x0833b05e in find_field_in_tables (thd=0x93efae0, item=0x94806c0, first_table=0x94801f0, last_table=0x0,
    ref=0x9477f88, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true)
    at sql_base.cc:6581
#6 0x0820554a in Item_field::fix_fields (this=0x94806c0, thd=0x93efae0, reference=0x9477f88) at item.cc:4585
#7 0x0823f684 in Item_in_optimizer::fix_left (this=0x9477f28, thd=0x93efae0, ref=0xae8d8388)
    at item_cmpfunc.cc:1437
#8 0x0823face in Item_in_optimizer::fix_fields (this=0x9477f28, thd=0x93efae0, ref=0xae8d8388)
    at item_cmpfunc.cc:1485
#9 0x0833dce2 in setup_tables (thd=0x93efae0, context=0x94775f8, from_clause=0x9477688, tables=0x94813d0,
    leaves=..., select_insert=false, full_table_list=false) at sql_base.cc:8029
#10 0x0833de77 in setup_tables_and_check_access (thd=0x93efae0, context=0x94775f8, from_clause=0x9477688,
    tables=0x94813d0, leaves=..., select_insert=false, want_access_first=1, want_access=1,
    full_table_list=false) at sql_base.cc:8083
#11 0x083516ca in JOIN::prepare (this=0x94c6818, rref_pointer_array=0x9477700, tables_init=0x94813d0,
    wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0,
    proc_param_init=0x0, select_lex_arg=0x94775c4, unit_arg=0x94772e4) at sql_select.cc:591
#12 0x084b185d in st_select_lex_unit::prepare (this=0x94772e4, thd_arg=0x93efae0, sel_result=0x9478680,
    additional_options=0) at sql_union.cc:325
#13 0x084b4701 in mysql_derived_prepare (thd=0x93efae0, lex=0x947f698, derived=0x94801f0)
    at sql_derived.cc:625
#14 0x084b3e32 in mysql_handle_single_derived (lex=0x947f698, derived=0x94801f0, phases=2)
    at sql_derived.cc:176
#15 0x0834fa76 in TABLE_LIST::handle_derived (this=0x94801f0, lex=0x947f698, phases=2) at table.cc:5829
#16 0x081e7368 in st_select_lex::handle_derived (this=0x947f9d4, lex=0x947f698, phases=2) at sql_lex.cc:3170
#17 0x083515f3 in JOIN::prepare (this=0x94b9c28, rref_pointer_array=0x947fb10, tables_init=0x94801f0,
    wild_num=0, conds_init=0x94785f0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0,
    proc_param_init=0x0, select_lex_arg=0x947f9d4, unit_arg=0x947f6f4) at sql_select.cc:578
#18 0x0835925d in mysql_select (thd=0x93efae0, rref_pointer_array=0x947fb10, tables=0x94801f0, wild_num=0,
    fields=..., conds=0x94785f0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2416200193, result=0x9481158, unit=0x947f6f4, select_lex=0x947f9d4) at sql_select.cc:2932
#19 0x08350f7f in handle_select (thd=0x93efae0, lex=0x947f698, result=0x9481158, setup_tables_done_option=0)
    at sql_select.cc:285
#20 0x082ec085 in execute_sqlcom_select (thd=0x93efae0, all_tables=0x94801f0) at sql_parse.cc:5151
#21 0x082e3461 in mysql_execute_command (thd=0x93efae0) at sql_parse.cc:2284
#22 0x083a5bb6 in Prepared_statement::execute (this=0x947f388, expanded_query=0xae8d971c, open_cursor=false)
    at sql_prepare.cc:3732
#23 0x083a50cb in Prepared_statement::execute_loop (this=0x947f388, expanded_query=0xae8d971c,
    open_cursor=false, packet=0x0, packet_end=0x0) at sql_prepare.cc:3413
#24 0x083a3993 in mysql_sql_stmt_execute (thd=0x93efae0) at sql_prepare.cc:2638
#25 0x082e3487 in mysql_execute_command (thd=0x93efae0) at sql_parse.cc:2293
#26 0x082ee6c0 in mysql_parse (thd=0x93efae0, rawbuf=0x945ec10 "EXECUTE pstmt", length=13,
    found_semicolon=0xae8da234) at sql_parse.cc:6152
#27 0x082e10a1 in dispatch_command (command=COM_QUERY, thd=0x93efae0, packet=0x9449351 "EXECUTE pstmt",
    packet_length=13) at sql_parse.cc:1228
#28 0x082e054b in do_command (thd=0x93efae0) at sql_parse.cc:923
#29 0x082dd4d1 in handle_one_connection (arg=0x93efae0) at sql_connect.cc:1193
#30 0xb76efb25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-03-05 22:33:46 -0800
build-date: 2012-03-11 20:50:07 +0400
revno: 3455

Also reproducible on MariaDB 5.5 (revno 3316).
Not reproducible on MySQL 5.6 (trunk revno 3706).

EXPLAIN:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 Using temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Distinct; Using join buffer (incremental, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select distinct `test`.`t1`.`a` AS `a` from <materialize> (select min(`test`.`t2`.`b`) from `test`.`t2`) join `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `<subquery2>`.`MIN(b)`)

Minimal optimizer_switch: materialization=on,semijoin=on
Full optimizer_switch (default): 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=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch = 'materialization=on,semijoin=on';

CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (2),(3);
CREATE VIEW v1 AS SELECT * FROM t1;

CREATE TABLE t2 ( b VARCHAR(1) );
INSERT INTO t2 VALUES ('v'),('v');

PREPARE pstmt FROM
  'SELECT DISTINCT a FROM v1, t2
   WHERE b IN ( SELECT MIN(b) FROM t2 )';

EXECUTE pstmt;
EXECUTE pstmt;

[Sergey Petrunia (sergefp)]

EXPLAIN shows "Using join buffer" but that part is irrelevant, the crash happens without use of join buffer,too

[Sergey Petrunia (sergefp)]

Sorry, t he last comment was re #952297.

This bug is a manifestation of a different problem than #952297.

[Sergey Petrunia (sergefp)]

The crash itself happens because we have thd->lex->current_select point
to the view's subselect when we're running fix_fields() for the subquery's left
expression.

we're running that fix_fields(), because we're inside this code in
setup_tables():

    if (table_list->jtbm_subselect)
    {
      Item *item= table_list->jtbm_subselect->optimizer;
      if (table_list->jtbm_subselect->optimizer->fix_fields(thd, &item))
      {

(gdb) p table_list->alias
  $769 = 0x9f4fc00 "<subquery2>"

It seems wrong that we have reached the SJM nest while running setup_tables()
for the contents of the v1 view.

[Sergey Petrunia (sergefp)]

PREPARE pstmt FROM '
SELECT DISTINCT a FROM v1, t2a WHERE t2a.b IN ( SELECT MIN(b) FROM t2b where 123423< 999999 )
';

  v1 -- t2a - sjm(t2b)
   |
  t1

When the subquery is converted to JTBM nest in convert_subq_to_jtbm():

- parent_lex points to the top-level select
- The parent_lex->leaf_tables = {t1, t2a}
      parent_lex->top_join_list= {t2a, v1}

   (t1)->next_local= NULL
   (t2a)->next_local= NULL

then we run this code:

  /*
    Same as above for TABLE_LIST::next_local chain
    (a theory: a next_local chain always starts with ::leaf_tables
     because view's tables are inserted after the view)
  */
  for (tl= parent_lex->leaf_tables.head(); tl->next_local; tl= tl->next_local)
  {}
  tl->next_local= jtbm;

which does:
   (t1)->next_local= jtbm;

and this is the reason why a subsequent call of setup_tables() for the contents
of v1 VIEW finds the jtbm nest.

[Sergey Petrunia (sergefp)]

It seems, there are cases where this code

  for (tl= parent_lex->leaf_tables.head(); tl->next_local; tl= tl->next_local)
  {}
  tl->next_local= jtbm;

in convert_subq_to_jtbm() does the wrong thing. I am not currently sure what that should be replaced with.

[Sergey Petrunia (sergefp)]

Patch committed, need review