Assertion `!is_interleave_error' failed on low optimizer_search_depth

Bug #693329 reported by Philip Stoev on 2010-12-22
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Sergey Petrunia

Bug Description

As already reported in mysql bug , using low values for optimizer_search_depth causes an easy crash on multiple-table joins.

The RQG sometimes generates joins involving many tables. In order to restrict the total optimization time per query, and thus run more queries per test run, optimizer_search_depth must be safe to use and not assert.

mysqld: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `!is_interleave_error' failed.


#8 0x006b0d98 in __assert_fail () from /lib/
#9 0x0831785c in greedy_search (join=0xae688eb0, remaining_tables=55, search_depth=4, prune_level=1) at
#10 0x08316ebc in choose_plan (join=0xae688eb0, join_tables=55) at
#11 0x0831205f in make_join_statistics (join=0xae688eb0, tables_arg=0xae631c80, conds=0x0, keyuse_array=0xae68dbec) at
#12 0x08309e93 in JOIN::optimize (this=0xae688eb0) at
#13 0x0830fa81 in mysql_select (thd=0xb1fda70, rref_pointer_array=0xb1ff50c, tables=0xae631c80, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae64b8b0,
    unit=0xb1ff16c, select_lex=0xb1ff408) at
#14 0x0830800d in handle_select (thd=0xb1fda70, lex=0xb1ff110, result=0xae64b8b0, setup_tables_done_option=0) at
#15 0x082a3400 in execute_sqlcom_select (thd=0xb1fda70, all_tables=0xae631c80) at
#16 0x08299e1b in mysql_execute_command (thd=0xb1fda70) at
#17 0x082a599e in mysql_parse (thd=0xb1fda70,
    rawbuf=0xae631940 "SELECT table2.f4 FROM t1 AS table1 LEFT JOIN t1 AS table2 LEFT JOIN t2 AS table3 LEFT JOIN t3 AS table4 ON table3 .f1 = table4.f3 ON table2 .f1 LEFT JOIN t4 AS table5 JOIN t5 ON tab"...,
    length=262, found_semicolon=0xae983228) at
#18 0x08297904 in dispatch_command (command=COM_QUERY, thd=0xb1fda70,
    packet=0xb217c31 "SELECT table2.f4 FROM t1 AS table1 LEFT JOIN t1 AS table2 LEFT JOIN t2 AS table3 LEFT JOIN t3 AS table4 ON table3 .f1 = table4.f3 ON table2 .f1 LEFT JOIN t4 AS table5 JOIN t5 ON tab"...,
    packet_length=265) at
#19 0x08296d6e in do_command (thd=0xb1fda70) at
#20 0x08293e4a in handle_one_connection (arg=0xb1fda70) at
#21 0x00821919 in start_thread () from /lib/
#22 0x0076acce in clone () from /lib/

test case:

SET SESSION optimizer_search_depth = 4;

CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int) ;
INSERT IGNORE INTO t1 VALUES (0,0,2,0),(NULL,0,2,0);

CREATE TABLE t2 (f1 int) ;
CREATE TABLE t3 (f3 int,PRIMARY KEY (f3)) ;
CREATE TABLE t4 (f5 int) ;
CREATE TABLE t5 (f2 int) ;

SELECT alias2.f4 FROM t1 AS alias1
LEFT JOIN t1 AS alias2
        LEFT JOIN t2 AS alias3
        LEFT JOIN t3 AS alias4 ON alias3.f1 = alias4.f3
        ON alias2.f1
LEFT JOIN t4 AS alias5
        JOIN t5 ON alias5.f5
ON alias2.f3 ON alias1.f2 ;

Changed in maria:
assignee: nobody → Timour Katchaounov (timour)
milestone: none → 5.3
milestone: 5.3 → none
Philip Stoev (philip-stoev) wrote :

Timour reports this bug is in Sergey's code. A patch is available from the mysql bug report.

Changed in maria:
assignee: Timour Katchaounov (timour) → Sergey Petrunia (sergefp)
Philip Stoev (pstoev-askmonty) wrote :

Here is another test case:

--source include/
SET SESSION optimizer_search_depth=4;

CREATE TABLE t1 ( f6 int) ENGINE=InnoDB;

CREATE TABLE t2 ( f1 int) ;

CREATE TABLE t3 ( f4 int) ;

CREATE TABLE t4 ( f1 int) ENGINE=InnoDB;

CREATE TABLE t5 ( f1 int) ;


SELECT t2.f1
RIGHT JOIN t2 JOIN t3 ON t2.f1
LEFT JOIN t6 ON t5.f1 = t6.f3 ON t4.f1 ON t3.f4 ON t1.f6 ;

Changed in maria:
milestone: none → 5.2
status: New → Confirmed
Philip Stoev (pstoev-askmonty) wrote :

Still repeatable with

revision-id: <email address hidden>
date: 2011-04-04 12:38:08 +0400
build-date: 2011-04-06 15:50:31 +0300
revno: 2963
branch-nick: maria-5.3

Changed in maria:
importance: Undecided → High
Changed in maria:
status: Confirmed → In Progress
Changed in maria:
status: In Progress → Confirmed
Elena Stepanova (elenst) on 2012-04-05
tags: added: assertion optimizer
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers