bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed in EXPLAIN in maria-5.3-mwl89

[Philip Stoev]

Even after the fix for 609045, the following EXPLAIN query asserts as follows:

mysqld: sql_base.cc:1386: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed.

#8 0x00133d98 in __assert_fail () from /lib/libc.so.6
#9 0x082e0ebf in close_thread_table (thd=0xb35e0e8, table_ptr=0xb35e134) at sql_base.cc:1386
#10 0x082e09d2 in close_open_tables (thd=0xb35e0e8) at sql_base.cc:1203
#11 0x082e0d6b in close_thread_tables (thd=0xb35e0e8) at sql_base.cc:1361
#12 0x0829308e in dispatch_command (command=COM_QUERY, thd=0xb35e0e8, packet=0xb376789 "", packet_length=405) at sql_parse.cc:1644
#13 0x08291376 in do_command (thd=0xb35e0e8) at sql_parse.cc:902
#14 0x0828e454 in handle_one_connection (arg=0xb35e0e8) at sql_connect.cc:1154
#15 0x00bea919 in start_thread () from /lib/libpthread.so.0
#16 0x001edcbe in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Test case:

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

CREATE TABLE t2 (f2 int,f4 int,f5 int) ;
INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0);

CREATE TABLE t3 (f4 int,KEY (f4)) ;
INSERT IGNORE INTO t3 VALUES (0),(0);

EXPLAIN
SELECT f3 FROM t1 WHERE
(
SELECT SUBQUERY2_t1.f1
FROM t1 AS SUBQUERY2_t1
RIGHT JOIN t1 AS SUBQUERY2_t2 JOIN t2 AS SUBQUERY2_t3 ON /* SUBQUERY2_t3.f5 = SUBQUERY2_t2.f4 AND */ SUBQUERY2_t3.f4 <= ALL
(
SELECT CHILD_SUBQUERY1_t1.f4 AS CHILD_SUBQUERY1_field1
FROM t3 AS CHILD_SUBQUERY1_t1 JOIN t3 AS CHILD_SUBQUERY1_t3 ON CHILD_SUBQUERY1_t3.f4
GROUP BY child_subquery1_field1 )
ON SUBQUERY2_t3.f2 )
;

[Timour Katchaounov (timour)]

Reduced test case:

set @@optimizer_switch='semijoin=off';

EXPLAIN
SELECT * FROM t1 WHERE
(SELECT f1 FROM t2
 WHERE f4 <= ALL
       (SELECT SQ1_t1.f4
        FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
        GROUP BY SQ1_t1.f4));

[Timour Katchaounov (timour)]

Analysis:
The cause for the failed ASSERT is in JOIN::join_free, where we set
  bool full= (!select_lex->uncacheable && !thd->lex->describe);
Thus for EXPLAIN statements full == FALSE, and as a result the call to
JOIN::cleanup doesn't call JOIN_TAB::cleanup which should have
called table->disable_keyread().

[Timour Katchaounov (timour)]

The reason that subqueries are evaluated during EXPLAIN in this case, is
because single-row subqueries are not currently considered expensive.

A proper fix of this bug should consist of two parts:
a) make all subqueries expensive, because the assumption that single-row
   ones are not expensive is generally wrong.
b) make it possible to execute subqueries and do proper cleanup after them,
   because in the future we may properly detect that some subqueries are
   really cheap, and decide to execute them during EXPLAIN.