Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) with semijoin+materialization on EXPLAIN

[Elena Stepanova]

#8 0xb747c014 in __assert_fail () from /lib/libc.so.6
#9 0x0824b839 in Item_equal::get_first (this=0x946ef38, context=0x94bf138, field_item=0x945e2c0) at item_cmpfunc.cc:5899
#10 0x08205fe4 in Item_field::replace_equal_field (this=0x945e2c0, arg=0xae8f3fc0 "8\357F\t8\361K\t\274\355F\tP\360F\t")
    at item.cc:5038
#11 0x081fbd55 in Item::transform (this=0x945e2c0, transformer=&virtual Item::replace_equal_field(uchar*),
    arg=0xae8f3fc0 "8\357F\t8\361K\t\274\355F\tP\360F\t") at item.cc:647
#12 0x0836c6de in substitute_for_best_equal_field (context_tab=0x94bf138, cond=0x945e2c0, cond_equal=0x946edb4,
    table_join_idx=0x94be150) at sql_select.cc:11702
#13 0x08353862 in JOIN::optimize (this=0x9476628) at sql_select.cc:1271
#14 0x08358dc0 in mysql_select (thd=0x93edad0, rref_pointer_array=0x93ef65c, tables=0x945dba8, wild_num=1, fields=...,
    conds=0x946d760, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x946d888,
    unit=0x93ef240, select_lex=0x93ef520) at sql_select.cc:2917
#15 0x083838e1 in mysql_explain_union (thd=0x93edad0, unit=0x93ef240, result=0x946d888) at sql_select.cc:21381
#16 0x082ebf1a in execute_sqlcom_select (thd=0x93edad0, all_tables=0x945dba8) at sql_parse.cc:5127
#17 0x082e3474 in mysql_execute_command (thd=0x93edad0) at sql_parse.cc:2281
#18 0x082ee6d3 in mysql_parse (thd=0x93edad0,
    rawbuf=0x945cc20 "EXPLAIN \nSELECT * FROM \n( SELECT * FROM t1 ) AS alias1, \nt2 AS alias2 \nWHERE b = a AND a IN ( \nSELECT alias3.c \nFROM t2 AS alias3, t2 AS alias4 \nWHERE alias4.c = alias3.b \n)", length=175, found_semicolon=0xae8f5234)
    at sql_parse.cc:6149
#19 0x082e10d6 in dispatch_command (command=COM_QUERY, thd=0x93edad0, packet=0x9447361 "", packet_length=175)
    at sql_parse.cc:1227
#20 0x082e0580 in do_command (thd=0x93edad0) at sql_parse.cc:922
#21 0x082dd545 in handle_one_connection (arg=0x93edad0) at sql_connect.cc:1193
#22 0xb770ab25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-01-25 22:05:20 +0400
build-date: 2012-01-26 23:04:21 +0400
revno: 3395
branch-nick: maria-5.3

Also reproducible on revno 3390, but isn't reproducible on 3389.

Minimal switch: semijoin=on,materialization=on
Full 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=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 = 'semijoin=on,materialization=on';

CREATE TABLE t1 ( a VARCHAR(3) );
CREATE TABLE t2 ( b VARCHAR(3), c VARCHAR(8), KEY(c) );
INSERT INTO t2 VALUES ('USA','Abilene'),('USA','Akron');

EXPLAIN
SELECT * FROM
  ( SELECT * FROM t1 ) AS alias1,
  t2 AS alias2
WHERE b = a AND a IN (
  SELECT alias3.c
  FROM t2 AS alias3, t2 AS alias4
  WHERE alias4.c = alias3.b
);

[Elena Stepanova (elenst)]

There were 5 assertion failures (non-simplified variations of the test case) during the test run.

[Sergey Petrunia (sergefp)]

The system constructs a join order of:

t1, i.e. alias1 (the const table)
<sj-materialize>
    alias3
    alias4
alias2

access to table alias3 is made via

   alias3.c = alias1.a

This is correct: alias1.a is a constant, so this is a ref(const) access.

The system then tries to perform equality substitution for "alias1.a" within
the context of table alias3.

Eventually, it will call this function:
Item *Item_field::replace_equal_field(uchar *arg)

and reach this code:

    Item *const_item= item_equal->get_const();

and here we will have:

(gdb) p item_equal->with_const
  $101 = false

... which is surprising, considering that the item_equal consists of (in order):
  alias1.a
  alias2.b

Eventually, we will reach

Item* Item_equal::get_first(JOIN_TAB *context, Item *field_item)

with context = { alias3 }
and field_item= {t1.a}

and there we will fail an assert because we were unable to find a member of
Item_equal that could be evaluated within the semi-join nest.

[Sergey Petrunia (sergefp)]

Committed a fix.