[MDEV-3630] LP:802979 - Assertion `table->key_read == 0' in close_thread_table in maria-5.3 Created: 2011-06-28  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug802979.xml    

 Description   

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

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-06-28 ]

Re: Assertion `table->key_read == 0' in close_thread_table in maria-5.3
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

Comment by Timour Katchaounov (Inactive) [ 2011-06-29 ]

Re: Assertion `table->key_read == 0' in close_thread_table in maria-5.3
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.

Comment by Timour Katchaounov (Inactive) [ 2011-06-30 ]

Re: Assertion `table->key_read == 0' in close_thread_table in maria-5.3
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.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 802979

Generated at Thu Feb 08 06:50:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.