Crash in Item_equal_fields_iterator::get_curr_field with semijoin+materialization

[Philip Stoev]

The following query:

SELECT *
FROM t2
WHERE t2.a = ALL (
        SELECT t4.a
        FROM t4
        WHERE t4.a IN (
                SELECT t3.a
                FROM t3 , t5
                WHERE ( t5.a = t3.b )
        )
);

crashes as follows:

#3 <signal handler called>
#4 0x00000000005f656c in Item_equal_fields_iterator::get_curr_field (this=0x410d9e60) at item_cmpfunc.h:1790
#5 0x00000000005e5bd3 in Item_equal::contains (this=0x1f0fd0b8, field=0x1f0903f8) at item_cmpfunc.cc:5431
#6 0x0000000000734fbe in find_item_equal (cond_equal=0x1f0f9400, field=0x1f0903f8, inherited_fl=0x410d9fde) at sql_select.cc:10301
#7 0x0000000000806702 in setup_sj_materialization_part2 (sjm_tab=0x1f0fbff0) at opt_subselect.cc:3144
#8 0x000000000074ae5c in make_join_readinfo (join=0x1f0ec4f0, options=0, no_jbuf_after=3) at sql_select.cc:9294
#9 0x000000000074e369 in JOIN::optimize (this=0x1f0ec4f0) at sql_select.cc:1497
#10 0x000000000057ab4e in st_select_lex::optimize_unflattened_subqueries (this=0x1efdbdc8) at sql_lex.cc:3126
#11 0x00000000008038c0 in JOIN::optimize_unflattened_subqueries (this=0x1f0e62d0) at opt_subselect.cc:4318
#12 0x000000000074eac1 in JOIN::optimize (this=0x1f0e62d0) at sql_select.cc:1622
#13 0x00000000007505bb in mysql_select (thd=0x1efd9438, rref_pointer_array=0x1efdc018, tables=0x1f05e0c0, wild_num=1, fields=..., conds=0x1f09a710,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x1f09a898, unit=0x1efdb8e0, select_lex=0x1efdbdc8)
    at sql_select.cc:2887
#14 0x0000000000756a7a in handle_select (thd=0x1efd9438, lex=0x1efdb840, result=0x1f09a898, setup_tables_done_option=0) at sql_select.cc:283
#15 0x00000000006a33de in execute_sqlcom_select (thd=0x1efd9438, all_tables=0x1f05e0c0) at sql_parse.cc:5090
#16 0x00000000006a50bc in mysql_execute_command (thd=0x1efd9438) at sql_parse.cc:2234
#17 0x00000000006ade55 in mysql_parse (thd=0x1efd9438,
    rawbuf=0x1f05de10 "SELECT *\nFROM t2\nWHERE t2.a = ALL (\nSELECT t4.a\nFROM t4\nWHERE t4.a IN (\nSELECT t3.a\nFROM t3 , t5\nWHERE ( t5.a = t3.b )\n)\n)", length=122, found_semicolon=0x410dbf08) at sql_parse.cc:6091
#18 0x00000000006aed25 in dispatch_command (command=COM_QUERY, thd=0x1efd9438,
    packet=0x1f0549a9 "SELECT *\nFROM t2\nWHERE t2.a = ALL (\nSELECT t4.a\nFROM t4\nWHERE t4.a IN (\nSELECT t3.a\nFROM t3 , t5\nWHERE ( t5.a = t3.b )\n)\n)", packet_length=122) at sql_parse.cc:1211
#19 0x00000000006b0333 in do_command (thd=0x1efd9438) at sql_parse.cc:906
#20 0x000000000069ac67 in handle_one_connection (arg=0x1efd9438) at sql_connect.cc:1186
#21 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#22 0x00000033b58d40cd in clone () from /lib64/libc.so.6

Explain also crashes. There are no empty or 1-row tables.

minimal optimizer switch: semijoin=ON,materialization=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,materialization=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,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-27 00:40:29 +0300
build-date: 2011-08-29 12:13:10 +0300
revno: 3167
branch-nick: maria-5.3

test case:

CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES ('a'),('a');

CREATE TABLE t4 (a varchar(1));
INSERT INTO t4 VALUES ('m'),('o');

CREATE TABLE t3 (a varchar(1) , b varchar(1) ) ;
INSERT INTO t3 VALUES ('b','b');

CREATE TABLE t5 (a varchar(1), KEY (a)) ;
INSERT INTO t5 VALUES ('d'),('e');

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

SELECT *
FROM t2
WHERE t2.a = ALL (
        SELECT t4.a
        FROM t4
        WHERE t4.a IN (
                SELECT t3.a
                FROM t3 , t5
                WHERE ( t5.a = t3.b )
        )
);

[Sergey Petrunia (sergefp)]

The crash is here:

(gdb) wher
  #0 0x0822cd38 in Item_equal_fields_iterator::get_curr_field (this=0x90434bcc) at item_cmpfunc.h:1790
  #1 0x0821d9cc in Item_equal::contains (this=0xb1fbb00, field=0xb1f2338) at item_cmpfunc.cc:5438
  #2 0x0835e4ad in find_item_equal (cond_equal=0xb1f945c, field=0xb1f2338, inherited_fl=0x90434cea) at sql_select.cc:10313
  #3 0x08424d6d in setup_sj_materialization_part2 (sjm_tab=0xb1fb084) at opt_subselect.cc:3150
  #4 0x083616f3 in make_join_readinfo (join=0xb201db8, options=4, no_jbuf_after=3) at sql_select.cc:9306
  #5 0x08371c61 in JOIN::optimize (this=0xb201db8) at sql_select.cc:1497
  #6 0x081b96cb in st_select_lex::optimize_unflattened_subqueries (this=0xb184938) at sql_lex.cc:3126
  #7 0x08421fdb in JOIN::optimize_unflattened_subqueries (this=0xb1fbe40) at opt_subselect.cc:4324
  #8 0x08371cf2 in JOIN::optimize (this=0xb1fbe40) at sql_select.cc:1504
  #9 0x08373a40 in mysql_select (...) at sql_select.cc:2887

(gdb) up
  #1 0x0821d9cc in Item_equal::contains (this=0xb1fbb00, field=0xb1f2338) at item_cmpfunc.cc:5438
(gdb) p this
  $306 = (Item_func_eq *) 0xb1fbb00
         ^^^^^^^^^^^^^^^^^ How come this is an Item_func_eq, while we are
         in Item_equal's function?
         Item_equal and Item_func_eq do not inherit from one another, something
         is clearly wrong.

(gdb) up
  #1 0x0821d9cc in Item_equal::contains (this=0xb1fbb00, field=0xb1f2338) at item_cmpfunc.cc:5438
(gdb) p this
  $306 = (Item_func_eq *) 0xb1fbb00
(gdb) up
  #2 0x0835e4ad in find_item_equal (cond_equal=0xb1f945c, field=0xb1f2338, inherited_fl=0x90434cea) at sql_select.cc:10313
(gdb) p cond_equal->current_level.head()
  $307 = (Item_func_eq *) 0xb1fbb00

(gdb) p &cond_equal->current_level
  $309 = (List<Item_equal> *) 0xb1f9464

(gdb) p cond_equal->current_level.head()
  $307 = (Item_func_eq *) 0xb1fbb00

^^^ So we went up and see that cond_equal->current_level, which is of type
List<Item_equal> somehow ended up containing an Item_func_eq.

[Sergey Petrunia (sergefp)]

The reason for this is that when we were doing substitute-for-best-equal field operation here:

  #0 substitute_for_best_equal_field (cond=0xb1fad58, cond_equal=0xb1fadf4, table_join_idx=0xb1f9f10) at sql_select.cc:11412
  #1 0x08370cda in JOIN::optimize (this=0xb201db8) at sql_select.cc:1193
  #2 0x081b96cb in st_select_lex::optimize_unflattened_subqueries (this=0xb184938) at sql_lex.cc:3126
  #3 0x08421fdb in JOIN::optimize_unflattened_subqueries (this=0xb1fbe40) at opt_subselect.cc:4324
  #4 0x08371cf2 in JOIN::optimize (this=0xb1fbe40) at sql_select.cc:1504
  #5 0x08373a40 in mysql_select (...) at sql_select.cc:2887

and 'cond' was an Item_cond_and with the list of these four arguments:

  (gdb) p $i1
    $188 = (Item_cond_and *) 0xb1f93c0
  (gdb) p $i2
    $189 = (Item_func_trig_cond *) 0xb1fa930
  (gdb) p $i3
    $190 = (Item_equal *) 0xb1f95d0
  (gdb) p $i4
    $191 = (Item_equal *) 0xb1f96f0

the two last two elements of the list:

(gdb) p ((Item*)cond)->list->first->next->next
  $299 = (list_node *) 0xb1f96e8

(gdb) p ((Item*)cond)->list->first->next->next->next
  $300 = (list_node *) 0xb1f9808

WERE THE SAME AS JOIN's cond_equal:

(gdb) p this->cond_equal_
  $303 = (COND_EQUAL *) 0xb1f945c

(gdb) p this->cond_equal_->current_level->first
  $304 = (list_node *) 0xb1f96e8

(gdb) p this->cond_equal_->current_level->first->next
  $305 = (list_node *) 0xb1f9808

That is, List<Item> and List<Item_equal> somehow ended up sharing the tail of the list. substitute_for_best_equal_field() eventually executed this part of its code:

      /*
        This works OK with PS/SP re-execution as changes are made to
        the arguments of AND/OR items only
      */
      if (new_item != item)
        li.replace(new_item);

which is ok for the List<Item> but made List<Item_equal> JOIN::cond_equal_ invalid.

[Sergey Petrunia (sergefp)]

The lists get merged tails here:

(gdb) wher
  #0 Item_in_subselect::inject_in_to_exists_cond (..) at item_subselect.cc:2271
  #1 0x08421c06 in JOIN::choose_subquery_plan (this=0xba79100, join_tables=5) at opt_subselect.cc:4645
  #2 0x0836f374 in make_join_statistics (...) at sql_select.cc:3545
  #3 0x08370b98 in JOIN::optimize (this=0xba79100) at sql_select.cc:1113
  #4 0x081b9aab in st_select_lex::optimize_unflattened_subqueries (this=0xba4fef0) at sql_lex.cc:3126
  #5 0x08422577 in JOIN::optimize_unflattened_subqueries (this=0xba73188) at opt_subselect.cc:4324
  #6 0x08372226 in JOIN::optimize (this=0xba73188) at sql_select.cc:1504
  #7 0x08373f74 in mysql_select (...) at sql_select.cc:2887

Here, we get this code:

    /* Attach back the list of multiple equalities to the new top-level AND. */
    if (and_args && join_arg->cond_equal_)
    {
      /* The argument list of the top-level AND may change after fix fields. */
      and_args= ((Item_cond*) join_arg->conds)->argument_list();
> and_args->concat((List<Item> *) &join_arg->cond_equal_->current_level);
    }
  }

and after the line marked with '>' executes, we get:
  Item_in_subselect::inject_in_to_exists_cond (this=0xba5d970, join_arg=0xba79100) at item_subselect.cc:2271
(gdb) p join_arg->cond_equal_->current_level->elements
  $320 = 2
(gdb) p join_arg->cond_equal_->current_level->first
  $321 = (list_node *) 0xba70a30
(gdb) p join_arg->cond_equal_->current_level->first->next
  $322 = (list_node *) 0xba70b50

(gdb) set $and_args=((class Item_cond*)join_arg->conds)->argument_list()
(gdb) p $and_args->first
  $328 = (list_node *) 0xba72150
(gdb) p $and_args->first->next
  $329 = (list_node *) 0xba72158
(gdb) p $and_args->first->next->next
  $330 = (list_node *) 0xba70a30
(gdb) p $and_args->first->next->next->next
  $331 = (list_node *) 0xba70b50

i.e. the lists get mixed tails.