MySQL Bugs: #46811: Crash - Assertion failed: (table->key_read == 0), function close_thread

Bug #46811	Crash - Assertion failed: (table->key_read == 0), function close_thread_table
Submitted:	19 Aug 2009 14:49	Modified:	30 Sep 2009 12:58
Reporter:	Patrick Crews	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.4/6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	crashing bug, regression, subquery

Description:
Crashing bug in azalea:
Initial testing makes it seem as if this is *not* affected by optimizer_switch, optimizer_use_mrr, or join_cache_level variable settings.

This query (from the test case):
SELECT (  
SELECT SUBQUERY1_t1 .`varchar_key`  
FROM B SUBQUERY1_t1  STRAIGHT_JOIN BB  ON ( 'e' , 't' )  IN (  
SELECT `varchar_key`  ,  MAX( SUBQUERY1_t1 .`varchar_key`  )  
FROM A  )  )  , table1 .`time_key` field5  
FROM D table1  JOIN C  ON table1 .`varchar_key`  
ORDER  BY field5   ;

Is causing the following crash output (full crash output attached as separate file)
Assertion failed: (table->key_read == 0), function close_thread_table, file sql_base.cc, line 1522.
090819 17:40:26 - mysqld got signal 6 ;
<snip>
# 10:40:56 Thread 11 (core thread 10):
# 10:40:56 #0  0x90efe136 in clock_get_attributes ()
# 10:40:56 #1  0x90ef7013 in usleep$NOCANCEL$UNIX2003 ()
# 10:40:56 #2  0x90f0e685 in abort ()
# 10:40:56 #3  0x90f033db in __assert_rtn ()
# 10:40:56 #4  0x0016734a in close_thread_table (thd=0x124b218, table_ptr=0x124b264) at sql_base.cc:1522
# 10:40:56 #5  0x0016761b in close_open_tables (thd=0x124b218) at sql_base.cc:1255
# 10:40:56 #6  0x00167a26 in close_thread_tables (thd=0x124b218) at sql_base.cc:1498
# 10:40:56 #7  0x001211ed in dispatch_command (command=COM_QUERY, thd=0x124b218, packet=0x19f0019 "", packet_length=261) at sql_parse.cc:1412

How to repeat:
MTR test case is attached as a separate file.

I apologize for the size of the tables that are being used in the test - the query simplification tool was having some issues.

Run the attached test and observe the crash / failure.

Suggested fix:
Ensure crash-free query processing.

MTR test case to repeat the bug

Attachment: bug46811_test.txt (text/plain), 17.56 KiB.

Unable to reproduce the crash on both Solaris and Linux using the attached test case with the latest source code from mysql-6.0-codebase-bugfixing. 

Plan to add the test case to one of the existing tests.

Simplified version of the original MTR test case

Attachment: bug46811.test.txt (text/plain), 1.07 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84956

3627 Olav Sandstaa	2009-09-29
      Bug #46811 - Crash - Assertion failed: (table->key_read == 0), function 
      close_thread_table
      
      Crash was no longer reproducable. This patch adds a regression test
      for the query that caused the server to crash.
     @ mysql-test/r/subselect.result
        Add regression test for Bug#46811.
     @ mysql-test/r/subselect_no_mat.result
        Add regression test for Bug#46811.
     @ mysql-test/r/subselect_no_opts.result
        Add regression test for Bug#46811.
     @ mysql-test/r/subselect_no_semijoin.result
        Add regression test for Bug#46811.
     @ mysql-test/t/subselect.test
        Add regression test for Bug#46811.

this bug was indeed fixed by
joro@sun.com-20090903150346-hhpg12ayy8vogmzl , the comment of this revision is:

Bug #46791: Assertion failed:(table->key_read==0),function unknown
function,file sql_base.cc
etc.

Closing this as a duplicate of Bug#46791 which contains both a fix and a test case for this crash.