Assertion `table->key_read == 0' in close_thread_table in maria-5.3

[Philip Stoev]

Repeatable with maria-5.3, maria-5.3-subqueries-wl90. Reproducible even after 609045. Reproducible with all relevant swiches set to OFF.

assertion:

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

#6 0x00000033b58296e6 in __assert_fail () from /lib64/libc.so.6
#7 0x000000000070012c in close_thread_table (thd=0xcc6f098, table_ptr=0xcc6f150) at sql_base.cc:1387
#8 0x0000000000700873 in close_open_tables (thd=0xcc6f098) at sql_base.cc:1204
#9 0x0000000000700d29 in close_thread_tables (thd=0xcc6f098) at sql_base.cc:1362
#10 0x00000000006ae270 in dispatch_command (command=COM_QUERY, thd=0xcc6f098, packet=0xcceb8c9 "", packet_length=152) at sql_parse.cc:1590
#11 0x00000000006ae635 in do_command (thd=0xcc6f098) at sql_parse.cc:904
#12 0x00000000006990eb in handle_one_connection (arg=0xcc6f098) at sql_connect.cc:1178
#13 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#14 0x00000033b58d40cd in clone () from /lib64/libc.so.6

optimizer switch in effect:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on

test case:

CREATE TABLE t1 ( f1 int, f2 int , KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (1,0),(5,0);

EXPLAIN
SELECT *
FROM t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2
WHERE alias3.f2 =
(
SELECT f2
FROM t1
 WHERE EXISTS
(
SELECT DISTINCT f1
FROM t1 )
 ) ;

[Philip Stoev (pstoev-askmonty)]

Not repeatable in maria-5.2. Explain in maria-5.2:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias2 ALL NULL NULL NULL NULL 2
1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
3 SUBQUERY t1 index NULL f1 5 NULL 2 Using index; Using temporary

[Timour Katchaounov (timour)]

The crash appears only in the EXPLAIN statement.
When the query is executed, it produces an error:

ERROR 1242 (21000): Subquery returns more than 1 row

My hypothesis is, that because the optimizer detects early that the
subquery cannot be executed, during explain either some needed
structures are not setup properly, or due to the early exit, proper
cleanup of the JOIN object is not preformed.

[Timour Katchaounov (timour)]

While I have a fix for the original test case, there is another one that breaks in a different manner.
Adding LIMIT 1 to the outer subquery:

EXPLAIN
SELECT *
FROM t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2
WHERE alias3.f2 = (SELECT f2 FROM t1
                                     WHERE EXISTS (SELECT DISTINCT f1 FROM t1 ) LIMIT 1) ;

Both the original, and this test case crash as a result of evaluating single-row subqueries
during the optimization phase of the outer query. During subquery execution, the original
table in the inner-most subquery is substituted by JOIN::make_simple_join by a temporary
table to perform the DISTINCT operation. This substitution breaks EXPLAIN because:
a) in the first example JOIN::cleanup no longer can reach the original table of the innermost
  subquery, and close all indexes, and
b) in this second test query, EXPLAIN attempts to print the name of the internal temp table,
  and crashes because there is no name (NULL pointer instead).

The solution is two-fold: fully disable subquery evaluation during optimization in all cases,
and change JOIN::join_free() to perform cleanup irrespective of EXPLAIN or not.