MySQL Bugs: #58494: Assertion !check_interleaving_with_nj(s) failed in optimize_straight

Bug #58494	Assertion !check_interleaving_with_nj(s) failed in optimize_straight_join()
Submitted:	25 Nov 2010 13:50	Modified:	13 Jan 2011 21:22
Reporter:	John Embretsen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.6.1-m5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	straight_join

Description:
While executing a nested JOIN query with the STRAIGHT_JOIN keyword, mysqld asserted as follows:

Assertion failed: !check_interleaving_with_nj(s), file sql/sql_select.cc, line 7653
101124 19:14:27 - mysqld got signal 6 ;

Stacktrace (from Solaris/dbx):

  [12] _assert(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff1fdc95
  [13] optimize_straight_join(join = 0x466d790, join_tables = 106ULL), line 7653 in "sql_select.cc"
  [14] choose_plan(join = 0x466d790, join_tables = 127ULL), line 7438 in "sql_select.cc"
  [15] make_join_statistics(join = 0x466d790, tables_arg = 0x463a028, conds = 0x466e0e8, keyuse_array = 0x466d9f8), line 4980 in "sql_select.cc"
  [16] JOIN::optimize(this = 0x466d790), line 1951 in "sql_select.cc"
  [17] mysql_select(thd = 0x45eea00, rref_pointer_array = 0x45f0e20, tables = 0x463a028, wild_num = 0, fields = CLASS, conds = 0x4669cc0, og_num = 0, order = (nil), group = (nil), having = (nil), proc_param = (nil), select_options = 2147748610ULL, result = 0x4669e60, unit = 0x45f0600, select_lex = 0x45f0c38), line 3484 in "sql_select.cc"
  [18] handle_select(thd = 0x45eea00, lex = 0x45f0550, result = 0x4669e60, setup_tables_done_option = 0), line 311 in "sql_select.cc"
  [19] execute_sqlcom_select(thd = 0x45eea00, all_tables = 0x463a028), line 4493 in "sql_parse.cc"
  [20] mysql_execute_command(thd = 0x45eea00), line 2089 in "sql_parse.cc"
  [21] mysql_parse(thd = 0x45eea00, rawbuf = 0x4639bd0 "SELECT STRAIGHT_JOIN table2.pk \nFROM t6 AS table2 \nLEFT JOIN t2 \nRIGHT JOIN t4 AS table5 JOIN t3 ON table5.col_int \nRIGHT JOIN t1 AS table9 ON table5.col_int_key \nLEFT JOIN t5 AS table11 JOIN t6 AS table12 ON table11.col_varchar_10_utf8_key ON table9.pk ON table5.col_varchar_10_latin1_key ON table2.pk \nWHERE table12.col_int_key", length = 329U, parser_state = 0xfffffd7fff07da88), line 5537 in "sql_parse.cc"
  [22] dispatch_command(command = COM_QUERY, thd = 0x45eea00, packet = 0x4631b81 "SELECT STRAIGHT_JOIN table2.pk \nFROM t6 AS table2 \nLEFT JOIN t2 \nRIGHT JOIN t4 AS table5 JOIN t3 ON table5.col_int \nRIGHT JOIN t1 AS table9 ON table5.col_int_key \nLEFT JOIN t5 AS table11 JOIN t6 AS table12 ON table11.col_varchar_10_utf8_key ON table9.pk ON table5.col_varchar_10_latin1_key ON table2.pk \nWHERE table12.col_int_key ", packet_length = 330U), line 1075 in "sql_parse.cc"
  [23] do_command(thd = 0x45eea00), line 815 in "sql_parse.cc"
  [24] do_handle_one_connection(thd_arg = 0x45eea00), line 745 in "sql_connect.cc"
  [25] handle_one_connection(arg = 0x45eea00), line 684 in "sql_connect.cc"
  [26] pfs_spawn_thread(arg = 0x4610680), line 1078 in "pfs.cc"
  [27] _thrp_setup(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27acf5
  [28] _lwp_start(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27afb0

The line numbers from above are from mysql-next-mr-opt-team as of 2010-11-25. The issue is also present in mysql-trunk-bugfixing and mysql-next-mr-opt-backporting.

Issue does not seem to be present in 5.1 or 5.5 branches.

How to repeat:
A simplified test case will be provided later.

Original crashing query (generated by the Random Query Generator):

SELECT  STRAIGHT_JOIN  table2 . `pk` AS field1 
FROM  R AS table1  
  LEFT OUTER JOIN  L AS table2  
    LEFT OUTER JOIN  DD AS table3 
      RIGHT OUTER JOIN L AS table4  
        LEFT OUTER JOIN AA AS table5
          LEFT JOIN V AS table6 ON table5.`col_int` =  table6.col_int_key`
          RIGHT JOIN  BB AS table7
            LEFT JOIN  DD AS table8
              RIGHT OUTER JOIN W AS table9 
              ON table8.col_varchar_1024_utf8_key` = table9.`col_varchar_10_utf8_key`
            ON  table7.`pk` =  table8.`col_int_key` 
          ON table5.`col_int_key` =  table8.`col_int`
        ON table4.`col_varchar_1024_utf8_key` =  table8.`col_varchar_10_latin1_key`
        LEFT OUTER JOIN F AS table10
          LEFT OUTER JOIN I AS table11
            RIGHT JOIN L AS table12 
            ON table11.`col_varchar_10_utf8_key` =  table12.col_varchar_1024_utf8_key`  
          ON table10.`col_varchar_1024_utf8_key` = table11.`col_varchar_10_latin1`
        ON table9.`pk` =  table11.`col_int`
      ON table3.`col_varchar_1024_latin1_key` =  table5.col_varchar_10_latin1_key`
    ON table2.`pk` =  table7.`col_int_key`
  ON table1.`col_varchar_1024_latin1_key` =  table7.`col_varchar_10_latin1`
WHERE ( table12 . `col_int_key` IS  NULL 
  AND table10.`pk` NOT BETWEEN 6 AND ( 6 + 6 ) )
GROUP BY field1 
HAVING field1 != 8
;

I added this assertion recently to the STRAIGHT_JOIN code path (optimize_straight_join()) (it was already present in the non-STRAIGHT_JOIN code path). I did it as a follow-up on BUG#52005.
This assertion is supposed to check for correctness of the chosen execution plan (sometimes even with STRAIGHT_JOIN the server cannot use the user-provided plan and switches to something else, so this assertion makes sense even with STRAIGHT_JOIN).

The following MTR-style test case reproduces this bug (for this test case InnoDB is required):

--source include/have_innodb.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS t6;
--enable_warnings

CREATE TABLE t1 (
  pk INT NOT NULL AUTO_INCREMENT,
  col_varchar_10_latin1_key VARCHAR(10),
  PRIMARY KEY (pk),
  KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
) ENGINE=MyISAM;

CREATE TABLE t2 (
  pk INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (pk)
) ENGINE=InnoDB;

CREATE TABLE t3 (
  pk INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (pk)
) ENGINE=InnoDB;

CREATE TABLE t4 (
  pk INT NOT NULL AUTO_INCREMENT,
  col_int INT,
  col_int_key INT,
  col_varchar_10_latin1_key VARCHAR(10),
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
) ENGINE=MyISAM;

CREATE TABLE t5 (
  col_int INT,
  col_varchar_10_utf8_key VARCHAR(10) CHARACTER SET utf8
) ENGINE=MyISAM;

CREATE TABLE t6 (
  col_int_key INT,
  col_varchar_10_latin1_key VARCHAR(10),
  pk INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (pk),
  KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key),
  KEY col_int_key (col_int_key)
) ENGINE=InnoDB;

SELECT STRAIGHT_JOIN t6a.pk
FROM t6 AS t6a
  LEFT JOIN t2
    RIGHT JOIN t4 
      JOIN t3 ON t4.col_int 
      RIGHT JOIN t1 ON t4.col_int_key = t1.pk
      LEFT JOIN t5 
        JOIN t6 AS t6b ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
      ON t1.pk = t5.col_int
    ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
  ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS t6;

Summary: I had added an assertion in 5.6 only, and this bug report is about a case where this assertion fires. If I remove the assertion there is no _visible_ problem anymore.
And if I add this assertion to 5.1 it fires as well.
This assertion is supposed to be correct, so I will investigate why it fires (in 5.6).

Though the testcase's query triggers an assertion failure in 5.6 and gives good results in 5.1/5.5, this is not a regression. The failed assertion, added to 5.6, points out that ordering of tables in the plan is incorrect and can theoretically lead to wrong results, and indeed a small modification of the testcase leads to wrong results in 5.1 and 5.5 too.
So this is a bug which exists since 5.1: filed as BUG#59487. Fixing 59487 will fix the assertion failure (58494). So 58494 is marked as duplicate of 59487.

Duplicate of BUG#59487