Crash in substitute_for_best_equal_field -> eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains

[Philip Stoev]

Not repeatable in Maria-5.3, though maria-5.3 suffers from a similar crash - bug 601124 . EXPLAIN also crashes.

backtrace:

#5 0x0820d246 in Item_equal::contains (this=0xae6675d8, field=0xae644090) at item_cmpfunc.cc:5618
#6 0x081caeca in Item_field::find_item_equal (this=0xae632da8, cond_equal=0xae66684c) at item.cc:4663
#7 0x0831bd86 in eliminate_item_equal (cond=0xae633070, upper_levels=0xae66684c, item_equal=0xae666c48) at sql_select.cc:9696
#8 0x0831c1fc in substitute_for_best_equal_field (cond=0xae633070, cond_equal=0xae63310c, table_join_idx=0xae667130) at sql_select.cc:9847
#9 0x0831c181 in substitute_for_best_equal_field (cond=0xae633130, cond_equal=0xae667b1c, table_join_idx=0xae667130) at sql_select.cc:9833
#10 0x0831c181 in substitute_for_best_equal_field (cond=0xae667a80, cond_equal=0xae667b1c, table_join_idx=0xae667130) at sql_select.cc:9833
#11 0x083068c2 in JOIN::optimize (this=0xae661470) at sql_select.cc:1026
#12 0x081aecc0 in st_select_lex::optimize_unflattened_subqueries (this=0xb3735d0) at sql_lex.cc:3140
#13 0x083c336b in JOIN::optimize_unflattened_subqueries (this=0xae65c5d8) at opt_subselect.cc:3617
#14 0x08307b44 in JOIN::optimize (this=0xae65c5d8) at sql_select.cc:1414
#15 0x0830bb0c in mysql_select (thd=0xb371c38, rref_pointer_array=0xb3736d4, tables=0xae631bb8, wild_num=1, fields=..., conds=0xae6331e0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae633380, unit=0xb373334, select_lex=0xb3735d0)
    at sql_select.cc:2653
#16 0x083045ed in handle_select (thd=0xb371c38, lex=0xb3732d8, result=0xae633380, setup_tables_done_option=0) at sql_select.cc:283
#17 0x082a20ac in execute_sqlcom_select (thd=0xb371c38, all_tables=0xae631bb8) at sql_parse.cc:5070
#18 0x082990bb in mysql_execute_command (thd=0xb371c38) at sql_parse.cc:2234
#19 0x082a4644 in mysql_parse (thd=0xb371c38,
    rawbuf=0xae631948 "SELECT * FROM t2\nWHERE ( f12 ) IN (\nSELECT alias2.f3\nFROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11\nWHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10\n)", length=168, found_semicolon=0xae9e1228) at sql_parse.cc:6077
#20 0x08296d53 in dispatch_command (command=COM_QUERY, thd=0xb371c38,
    packet=0xb389d69 "SELECT * FROM t2\nWHERE ( f12 ) IN (\nSELECT alias2.f3\nFROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11\nWHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10\n)", packet_length=168) at sql_parse.cc:1210
#21 0x08296200 in do_command (thd=0xb371c38) at sql_parse.cc:903
#22 0x082932de in handle_one_connection (arg=0xb371c38) at sql_connect.cc:1154
#23 0x00821919 in start_thread () from /lib/libpthread.so.0
#24 0x0076acce in clone () from /lib/libc.so.6

test case:

SET SESSION optimizer_switch = 'semijoin=off';

CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ;
INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d');

CREATE TABLE t2 ( f12 int(11), f13 int(11)) ;

SELECT * FROM t2
WHERE ( f12 ) IN (
        SELECT alias2.f3
        FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11
        WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10
);

[Timour Katchaounov (timour)]

Reproducible in 5.3-mwl89 with the following switch:
set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';

The bug is not repeatable with materialization.

[Timour Katchaounov (timour)]

This bug seems to be another instance of
https://bugs.launchpad.net/maria/+bug/641245

[Timour Katchaounov (timour)]

The source of the problem is in the call to Item_cond::fix_fields,
called on the WHERE clause of the subquery after injecting
the IN-TO-EXISTS condition into the subquery.

The relevant call stack is:
#0 Item_cond::fix_fields, item_cmpfunc.cc:4367
#1 Item_in_subselect::inject_in_to_exists_cond, item_subselect.cc:2063
#2 JOIN::choose_subquery_plan, opt_subselect.cc:3875
#3 make_join_statistics, sql_select.cc:3241
#4 JOIN::optimize, sql_select.cc:956
#5 st_select_lex::optimize_unflattened_subqueries, sql_lex.cc:3140
#6 JOIN::optimize_unflattened_subqueries, opt_subselect.cc:3617
#7 JOIN::optimize, sql_select.cc:1311

The call to Item_cond_and::fix_fields flattens the AND-OR structure
of the WHERE clause. The following line:
  li.replace(((Item_cond*) item)->list);
changes the contents of the list inner_join->cond_equal->current_level
and inserts an object of type Item_func_eq, while the list
COND_EQUAL::current_level may contain only items of type
Item_equal.

[Timour Katchaounov (timour)]

A bit simpler query (the braces are essential):

SELECT * FROM t2
WHERE ( f12 ) IN (
        SELECT alias2.f3
        FROM t1 AS alias1, t1 AS alias2
        WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10));

[Timour Katchaounov (timour)]

Analysis:

The core of the problem is that build_equal_items_for_cond() rewrites the WHERE
clause in such a way, that it may merge the list join->cond_equal->current_level
with the list of child Items in an AND condition of the WHERE clause.

The specific place where it done is:
static COND *build_equal_items_for_cond(THD *thd, COND *cond,
                                        COND_EQUAL *inherited)
{
  ...
      if (and_level)
    {
      args->concat(&eq_list);
      args->concat((List<Item> *)&cond_equal.current_level);
    }
  ...
}

As a result, later transformations on the WHERE clause may change the
structure of the list join->cond_equal->current_level without knowing this.

Solution:
Go over all places where the list join->cond_equal->current_level may be
become shared with any other list (e.g. AND nodes of the WHERE clause),
and instead of sharing the lists push new nodes into the target list.