MySQL Bugs: #46791: Assertion failed:(table->key_read==0),function unknown function,file sql

Bug #46791	Assertion failed:(table->key_read==0),function unknown function,file sql_base.cc
Submitted:	18 Aug 2009 17:40	Modified:	18 Dec 2009 13:18
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.82, 5.1, 5.4/6.0	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	crashing bug, subqueries

Description:
The following query (from attached test case) is causing a server crash:
SELECT   COUNT(  table2 . `pk` ) AS field1 FROM ( D AS table1 STRAIGHT_JOIN ( ( B AS table2 INNER JOIN CC AS table3 ON (table3 . `varchar_key` = table2 . `varchar_key`  ) ) ) ON (table3 . `varchar_key` = table2 . `varchar_key`  ) ) WHERE (  NOT EXISTS ( ( SELECT   SUBQUERY1_t1 . `varchar_key` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 INNER JOIN ( ( B AS SUBQUERY1_t2 STRAIGHT_JOIN C AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `varchar_key` = SUBQUERY1_t2 . `varchar_nokey` ) ) ) ON (( 'a', 'v' )  IN ( SELECT   SUBQUERY1_t1 . `varchar_key` AS SUBQUERY1_field1 , MIN( DISTINCT SUBQUERY1_t1 . `varchar_key` ) AS SUBQUERY1_field2 FROM ( A AS SUBQUERY1_t4 )    ) ) ) WHERE SUBQUERY1_t2 . `int_nokey` > 2  ) ) ) AND table1 . `int_key` > 49 AND table1 . `int_key` < ( 49 + 187 ) OR ( table1 . `pk` != table2 . `pk` AND table1 . `varchar_nokey` IS  NULL )  HAVING field1 <= 7 ORDER BY table1 . `pk` ASC , field1

Causes this crash output (pared down - full output is attached as separate file)
090818 19:38:44 [Note] <path>/mysql-5.1/sql/mysqld: ready for connections.
Version: '5.1.39-debug-log'  socket: '/var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-/xbsJ8z0VhD/master.sock'  port: 19306  Source distribution
Assertion failed: (table->key_read == 0), function unknown function, file sql_base.cc, line 1371.
090818 19:39:01 - mysqld got signal 6 ;

# 12:39:32 Thread 11 (core thread 10):
# 12:39:32 #0  0x90efe136 in clock_get_attributes ()
# 12:39:32 #1  0x90ef7013 in usleep$NOCANCEL$UNIX2003 ()
# 12:39:32 #2  0x90f0e685 in abort ()
# 12:39:32 #3  0x90f033db in __assert_rtn ()
# 12:39:32 #4  0x00159550 in close_thread_table (thd=0x1086618, table_ptr=0x1086664) at sql_base.cc:1371
# 12:39:32 #5  0x001598b0 in close_open_tables (thd=0x1086618) at sql_base.cc:1196
# 12:39:32 #6  0x00159c24 in close_thread_tables (thd=0x1086618) at sql_base.cc:1348
# 12:39:32 #7  0x0011c5fd in dispatch_command (command=COM_QUERY, thd=0x1086618, packet=0x19be019 "", packet_length=903) at sql_parse.cc:1587
# 12:39:32 #8  0x0011c9f2 in do_command (thd=0x1086618) at sql_parse.cc:854
# 12:39:32 #9  0x00107aa0 in handle_one_connection (arg=0x1086618) at sql_connect.cc:1127
# 12:39:32 #10 0x90e53155 in _pthread_start ()
# 12:39:32 #11 0x90e53012 in thread_start ()

How to repeat:
MTR test case is attached as a separate file - the test is too long for direct posting here per bugsdb

Suggested fix:
Ensure crash-free query processing.

Full crash output

Attachment: bug46791_crash_output.txt (text/plain), 15.68 KiB.

MTR test case to repeat the crash

Attachment: bug46791_test.txt (text/plain), 19.78 KiB.

NOTE - Will post a simplified test case when it is available.  Something here is complicating query simplification tools from running smoothly.

Have attached a simplified version of the test case to the bug.  It includes this query and the original, longer query.

Please note, that I think this is failing because of the following part:
 IN (  
SELECT `varchar_key`  ,  MIN( SUBQUERY1_t1 .`varchar_key`  )  
FROM A  ) 

We have already defined SUBQUERY1_t1 in a separate, earlier subquery.

SELECT table2 .`pk`  
FROM D table1  STRAIGHT_JOIN ( B table2  JOIN CC table3  ON table3 .`varchar_key`  )  ON table3 .`varchar_key`  
WHERE (  
SELECT SUBQUERY1_t1 .`varchar_key`  
FROM D SUBQUERY1_t1  JOIN B  ON ( 'a' , 'v' )  IN (  
SELECT `varchar_key`  ,  MIN( SUBQUERY1_t1 .`varchar_key`  )  
FROM A  )  )  OR table2 .`pk`  
ORDER  BY table1 .`pk`   ;

Full MTR test case with simplified and original queries

Attachment: bug46791_full_test.txt (text/plain), 39.58 KiB.

this crashed my 5.0.82-debug and 5.1.37-debug:

drop table  if exists  `a`,`d`;
create table `a` (`pk` int) engine=myisam;
create table `d` (`pk` int,`b` int,key(`pk`)) engine=myisam;
insert into d values (1,1),(2,2);
select  count(`pk`) as `field1`
from `d` where not exists 
( 
	select 1
	from `d`,`a` where 1 = (select min(`d`.`b`) from `a`) 
)
order by `pk`, `field1`;

Shane's test case haven't crashed for me with the latest 5.1-bugteam.

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/82338

2804 Georgi Kodinov	2009-09-03
      Bug #46791: Assertion failed:(table->key_read==0),function unknown
        function,file sql_base.cc
      
      When uncacheable queries are written to a temp table the optimizer must 
      preserve the original JOIN structure, because it is re-using the JOIN 
      structure to read from the resulting temporary table.
      This was done only for uncacheable sub-queries. 
      But top level queries can also benefit from this mechanism, specially if 
      they're using index access and need a reset.
      Fixed by not limiting the saving of JOIN structure to subqueries
      exclusively.
      Added a new test file to extend the existing (large) subquery.test.

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/82650

2809 Georgi Kodinov	2009-09-03
      Bug #46791: Assertion failed:(table->key_read==0),function unknown
        function,file sql_base.cc
      
      When uncacheable queries are written to a temp table the optimizer must 
      preserve the original JOIN structure, because it is re-using the JOIN 
      structure to read from the resulting temporary table.
      This was done only for uncacheable sub-queries. 
      But top level queries can also benefit from this mechanism, specially if 
      they're using index access and need a reset.
      Fixed by not limiting the saving of JOIN structure to subqueries
      exclusively.
      Added a new test file to extend the existing (large) subquery.test.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.0.87 (revid:joro@sun.com-20091006073202-rj21ggvo2gw032ks) (version source revid:kristofer.pettersson@sun.com-20090929151855-gvpblm4dnnubypdv) (merge vers: 5.0.87) (pib:11)

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)

Noted in 5.0.87, 5.1.40, 5.4.3 changelogs.

The server crashed when re-using outer column references in
correlated subqueries when the enclosing query used a temp table.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Already noted in earlier changelogs.