[MDEV-4536] mysqld: /my/maria-5.5/sql/sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed. Created: 2013-05-15  Updated: 2013-07-15  Resolved: 2013-07-15

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.2, 5.5.30
Fix Version/s: 10.0.4, 5.5.32

Type: Bug Priority: Major
Reporter: Oleksandr Byelkin Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Attachments: File 4536-unfinished_patch.diff     File unexplained_fix.patch    
Issue Links:
Duplicate
duplicates MDEV-4042 Assertion `table->key_read == 0' fail... Closed

 Description   

--source include/have_innodb.inc

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

CREATE TABLE t1 (pk int auto_increment primary key, `col_int_key` int(11),
key `col_int_key` (`col_int_key`),`col_varchar_key` varchar(128), key
(col_varchar_key)) engine=innodb;

EXPLAIN SELECT 1 FROM t1 AS alias1 WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) ) GROUP BY SQ2_field1 HAVING SQ2_alias1 . col_int_key >= 7 );

drop table t1;

 Comments   
Comment by Oleksandr Byelkin [ 2013-05-15 ]

It is important to have innodb table

Comment by Oleksandr Byelkin [ 2013-05-15 ]

It is not reproducable on 5.3

Comment by Elena Stepanova [ 2013-05-15 ]

We have one like that, MDEV-4042
Please make sure you're not doing double work with Sergei.

Comment by Oleksandr Byelkin [ 2013-05-15 ]

JOIN::cleanup() with full=1 called twice for both SELECTs...

Comment by Oleksandr Byelkin [ 2013-05-15 ]

in 5.5 (MyISAM) the EXPLAIN lack of table at all:
EXPLAIN SELECT 1 FROM t1 AS alias1 WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) ) GROUP BY SQ2_field1 HAVING SQ2_alias1 . col_int_key >= 7 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table

So this could be the root of the problem - final cleanup just do not see the tables to clean them up.

for comparison EXPLAIN from 5.3:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 index NULL PRIMARY 4 NULL 1 Using index
2 SUBQUERY SQ2_alias2 index NULL col_int_key 5 NULL 1 Using index; Using temporary; Using filesort
2 SUBQUERY SQ2_alias1 ref col_int_key col_int_key 5test.SQ2_alias2.col_int_key 1 Using where; Using index

Comment by Oleksandr Byelkin [ 2013-05-16 ]

fix found (also works for MDEV-4042). Now I am analizing what is fixed

Comment by Oleksandr Byelkin [ 2013-05-16 ]

Unexplained yet fix

Comment by Oleksandr Byelkin [ 2013-06-25 ]

unfinished patch

Comment by Oleksandr Byelkin [ 2013-06-25 ]

I attached the changes I made (not working yet for test suite passing)

Comment by Sergei Petrunia [ 2013-07-11 ]

note: it's only EXPLAIN that crashes. The SELECT runs normally (Just like with MDEV-4042).

Comment by Sergei Petrunia [ 2013-07-12 ]

Committed a patch. Passed to Serg for review and to Elena for testing

Comment by Sergei Petrunia [ 2013-07-15 ]

The patch was pushed and caused failures in buildbot

Comment by Sergei Petrunia [ 2013-07-15 ]

Pushed a patch that fixes failures observed by buildbot

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