Assertion `!is_interleave_error' failed on low optimizer_search_depth

Bug #693329 reported by Philip Stoev
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MariaDB
Confirmed
High
Sergey Petrunia

Bug Description

As already reported in mysql bug http://bugs.mysql.com/bug.php?id=54429 , 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.

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

backtrace:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0831785c in greedy_search (join=0xae688eb0, remaining_tables=55, search_depth=4, prune_level=1) at sql_select.cc:5498
#10 0x08316ebc in choose_plan (join=0xae688eb0, join_tables=55) at sql_select.cc:5120
#11 0x0831205f in make_join_statistics (join=0xae688eb0, tables_arg=0xae631c80, conds=0x0, keyuse_array=0xae68dbec) at sql_select.cc:3216
#12 0x08309e93 in JOIN::optimize (this=0xae688eb0) at sql_select.cc:956
#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 sql_select.cc:2607
#14 0x0830800d in handle_select (thd=0xb1fda70, lex=0xb1ff110, result=0xae64b8b0, setup_tables_done_option=0) at sql_select.cc:286
#15 0x082a3400 in execute_sqlcom_select (thd=0xb1fda70, all_tables=0xae631c80) at sql_parse.cc:5070
#16 0x08299e1b in mysql_execute_command (thd=0xb1fda70) at sql_parse.cc:2234
#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 sql_parse.cc:6077
#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 sql_parse.cc:1210
#19 0x08296d6e in do_command (thd=0xb1fda70) at sql_parse.cc:903
#20 0x08293e4a in handle_one_connection (arg=0xb1fda70) at sql_connect.cc:1154
#21 0x00821919 in start_thread () from /lib/libpthread.so.0
#22 0x0076acce in clone () from /lib/libc.so.6

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
Revision history for this message
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)
Revision history for this message
Philip Stoev (pstoev-askmonty) wrote :

Here is another test case:

--source include/have_innodb.inc
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) ;

CREATE TABLE t6 ( f3 int NOT NULL , PRIMARY KEY (f3)) ;

SELECT t2.f1
FROM t1
RIGHT JOIN t2 JOIN t3 ON t2.f1
RIGHT JOIN t4
LEFT JOIN t5
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
Revision history for this message
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)
tags: added: assertion optimizer
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.