Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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
);
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Crash in substitute_for_best_equal_field -> eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains
This bug seems to be another instance of
https://bugs.launchpad.net/maria/+bug/641245
Re: Crash in substitute_for_best_equal_field -> eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains
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.
Re: Crash in substitute_for_best_equal_field -> eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains
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));
Re: Crash in substitute_for_best_equal_field -> eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains
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)
...
}
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.
Launchpad bug id: 718593
Re: Crash in substitute_for_best_equal_field -> eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains
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.