MySQL Bugs: #75934: Assert `join()->tables == 0...` in JOIN_TAB::get_sj

Bug #75934	Assert `join()->tables == 0...` in JOIN_TAB::get_sj_strategy ON SUBQUERY + VAR
Submitted:	17 Feb 2015 15:41	Modified:	27 Mar 2015 3:17
Reporter:	John Embretsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When executing a subquery which includes an SQL variable, the server asserts as follows:

Version: '5.8.0-m17-debug-log'  socket: 'mysql-test/var/tmp/mysqld.1.sock'  port: 10020  Source distribution
mysqld: sql/sql_optimizer.cc:797:
 uint JOIN_TAB::get_sj_strategy() const:
 Assertion `join()->tables == 0 || (join()->best_ref && !join()->join_tab)' failed.
14:41:57 UTC - mysqld got signal 6 ;

Stacktrace from 5.8.0 (mysql-trunk Feb 17):

#2  handle_fatal_signal (sig=6) at sql/signal_handler.cc:221
#3  <signal handler called>
#4  __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#5  __GI_abort () at abort.c:89
#6  __assert_fail_base (fmt="%s%s%s:%u: %s%sAssertion `%s' failed.\n%n",
    assertion=assertion@entry="join()->tables == 0 || (join()->best_ref &&
    !join()->join_tab)", 
    file=file@entry="sql/sql_optimizer.cc", line=line@entry=797,
    function=function@entry=<JOIN_TAB::get_sj_strategy()
    const::__PRETTY_FUNCTION__> "uint JOIN_TAB::get_sj_strategy() const")
    at assert.c:92
#7  __GI___assert_fail (assertion="join()->tables == 0 || (join()->best_ref
    && !join()->join_tab)", file="sql/sql_optimizer.cc", line=797, 
    function=<JOIN_TAB::get_sj_strategy() const::__PRETTY_FUNCTION__>
    "uint JOIN_TAB::get_sj_strategy() const") at assert.c:101
#8  JOIN_TAB::get_sj_strategy (this=) at sql/sql_optimizer.cc:797
#9  Item_equal::get_subst_item (this=, field=) at sql/item_cmpfunc.cc:7308
#10 get_best_field (item_field=, cond_equal=) at sql/sql_optimizer.cc:3088
#11 create_ref_for_key (join=, j=, org_keyuse=, used_tables=2)
    at sql/sql_select.cc:1301
#12 JOIN::extract_func_dependent_tables (this=) at sql/sql_optimizer.cc:5196
#13 JOIN::make_join_plan (this=) at sql/sql_optimizer.cc:4667
#14 JOIN::optimize (this=) at sql/sql_optimizer.cc:350
#15 st_select_lex::optimize (this=, thd=) at sql/sql_select.cc:1002
#16 handle_query (thd=, lex=, result=, added_options=0, removed_options=0)
    at sql/sql_select.cc:162
#17 execute_sqlcom_select (thd=, all_tables=) at sql/sql_parse.cc:4695
#18 mysql_execute_command (thd=) at sql/sql_parse.cc:2408
#19 mysql_parse (thd=, parser_state=) at sql/sql_parse.cc:5112
#20 dispatch_command (command=COM_QUERY, thd=, 
    packet="SELECT table1.col_varchar_key AS field1

Seems to be repeatable only with MyISAM or MEMORY engines, not with InnoDB.
No crash with optimized build.
The variable in the query can be set to some value, but does not have to be in order to trigger the crash.

Verified against mysql-trunk (5.8.0) commit ada229f (Feb 17).
 and mysql-5.7 commit daf92c9 (Feb 17).

Not reproducible against mysql-5.6 branch commit f8249622 (Feb 17).

How to repeat:
MTR test based on failed RQG run using grammar optimizer/optimizer_subquery_portable.yy:

--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;
--enable_warnings

CREATE TABLE t1 (
  col_varchar varchar(1) DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_datetime_key datetime DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_datetime_key (col_datetime_key),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key)
) ENGINE=MyISAM;

CREATE TABLE t2 (  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  col_datetime_key datetime DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key),
  KEY col_datetime_key (col_datetime_key)
) ENGINE=MyISAM;

CREATE TABLE t3 (  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_datetime_key datetime DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key),
  KEY col_datetime_key (col_datetime_key)
) ENGINE=MyISAM;

INSERT INTO t3 VALUES (1,4,'c',NULL,'g',3);

INSERT INTO t3 VALUES (20,1,'i','2009-01-20 06:24:15','p',3);

CREATE TABLE t4 (  col_int_key int(11) DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_datetime_key datetime DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key),
  KEY col_datetime_key (col_datetime_key)
) ENGINE=MyISAM;

CREATE TABLE t5 (  col_varchar varchar(1) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_datetime_key datetime DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int int(11) DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key),
  KEY col_datetime_key (col_datetime_key),
  KEY col_int_key (col_int_key)
) ENGINE=MyISAM;

SELECT table1.col_varchar_key AS field1
FROM (t3 AS table1
  INNER JOIN (
    SELECT SUBQUERY1_t1.*
    FROM (t1 AS SUBQUERY1_t1
      RIGHT JOIN (t2 AS SUBQUERY1_t2
        INNER JOIN t3 AS SUBQUERY1_t3
        ON (SUBQUERY1_t3.col_varchar_key = SUBQUERY1_t2.col_varchar_key)
      ) ON (SUBQUERY1_t3.col_int_key = SUBQUERY1_t2.pk)
    )
    WHERE SUBQUERY1_t2.col_varchar_key >= ANY (
      SELECT CHILD_SUBQUERY1_t2.col_varchar AS CHILD_SUBQUERY1_field1
      FROM (t2 AS CHILD_SUBQUERY1_t1
        LEFT OUTER JOIN t3 AS CHILD_SUBQUERY1_t2
        ON (CHILD_SUBQUERY1_t2.col_int_key = CHILD_SUBQUERY1_t1.col_int_key)
      )
      WHERE CHILD_SUBQUERY1_t1.pk >= SUBQUERY1_t2.pk
    )
  ) AS table2
  ON (table2.pk = table1.pk)
)
WHERE (EXISTS ((
  SELECT DISTINCT SUBQUERY2_t2.col_int AS SUBQUERY2_field1
  FROM (t4 AS SUBQUERY2_t1
    LEFT OUTER JOIN t5 AS SUBQUERY2_t2
    ON (SUBQUERY2_t2.pk = SUBQUERY2_t1.col_int)
   )
   WHERE SUBQUERY2_t2.col_varchar_key != @var4
)))
  AND table1.col_int_key < (35 + 192)
ORDER BY field1
;

DROP TABLE t1, t2, t3, t4, t5;

Noted in 5.7.7, 5.8.0 changelogs.

A subquery that contained a user-defined variable could cause an
assertion to be raised.