MySQL Bugs: #52337: Assertion fail: close_thread_table () at sql

Bug #52337	Assertion fail: close_thread_table () at sql_base.cc:1375
Submitted:	24 Mar 2010 16:12	Modified:	26 Mar 2010 15:47
Reporter:	Patrick Crews	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	crash, regression

Description:
Assertion failing:
#8  0x001a3648 in *__GI___assert_fail (assertion=0x8ea0bd4 "!table->file || table->file->inited == handler::NONE", 
    file=0x8ea0680 "sql_base.cc", line=1375, function=0x8ea3500 "bool close_thread_table(THD*, TABLE**)") at assert.c:81
#9  0x0848ece7 in close_thread_table (thd=0xac597d8, table_ptr=0xac59824) at sql_base.cc:1375

This is happening in 5.1 only (tested against 6.0-codebase-bugfixing and 5.0-bugteam)

This query:
SELECT `col_varchar_key`  ,  MAX( `col_varchar_key`  )  
FROM D  
HAVING (  1  ,  2  )  IN (  
SELECT `pk`  , `col_int_key`  
FROM CC  
HAVING `col_int_key`  )   ;

Causes this trace (full trace attached separately due to space constraints):
Thread 1 (Thread 2889):
#0  0x003a0422 in __kernel_vsyscall ()
#1  0x00f70e93 in __pthread_kill (threadid=3069266800, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64
#2  0x08cbd8a6 in my_write_core (sig=6) at stacktrace.c:329
#3  0x083cfad2 in handle_segfault (sig=6) at mysqld.cc:2570
#4  <signal handler called>
#5  0x003a0422 in __kernel_vsyscall ()
#6  0x001aa4d1 in *__GI_raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#7  0x001ad932 in *__GI_abort () at abort.c:92
#8  0x001a3648 in *__GI___assert_fail (assertion=0x8ea0bd4 "!table->file || table->file->inited == handler::NONE", 
    file=0x8ea0680 "sql_base.cc", line=1375, function=0x8ea3500 "bool close_thread_table(THD*, TABLE**)") at assert.c:81
#9  0x0848ece7 in close_thread_table (thd=0xac597d8, table_ptr=0xac59824) at sql_base.cc:1375
#10 0x0848e109 in close_open_tables (thd=0xac597d8) at sql_base.cc:1199
#11 0x0848e9eb in close_thread_tables (thd=0xac597d8) at sql_base.cc:1351
#12 0x083f2b2f in dispatch_command (command=COM_QUERY, thd=0xac597d8, packet=0xac9f111 "", packet_length=162) at sql_parse.cc:1628
#13 0x083eec2f in do_command (thd=0xac597d8) at sql_parse.cc:874
#14 0x083eb29b in handle_one_connection (arg=0xac597d8) at sql_connect.cc:1127
#15 0x00f6b80e in start_thread (arg=0xb6f14b70) at pthread_create.c:300
#16 0x0024c8de in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130

How to repeat:
MTR test case:  Full test with original, unsimplified query attached separately

#/* Server0: MySQL 5.1.46-gcov-debug-log */

/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ D;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'v');
INSERT INTO `CC` VALUES (11,9,'r');
INSERT INTO `CC` VALUES (12,9,'a');
INSERT INTO `CC` VALUES (13,186,'m');
INSERT INTO `CC` VALUES (14,NULL,'y');
INSERT INTO `CC` VALUES (15,2,'j');
INSERT INTO `CC` VALUES (16,3,'d');
INSERT INTO `CC` VALUES (17,0,'z');
INSERT INTO `CC` VALUES (18,133,'e');
INSERT INTO `CC` VALUES (19,1,'h');
INSERT INTO `CC` VALUES (20,8,'b');
INSERT INTO `CC` VALUES (21,5,'s');
INSERT INTO `CC` VALUES (22,5,'e');
INSERT INTO `CC` VALUES (23,8,'j');
INSERT INTO `CC` VALUES (24,6,'e');
INSERT INTO `CC` VALUES (25,51,'f');
INSERT INTO `CC` VALUES (26,4,'v');
INSERT INTO `CC` VALUES (27,7,'x');
INSERT INTO `CC` VALUES (28,6,'m');
INSERT INTO `CC` VALUES (29,4,'c');
CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,NULL,'r');
INSERT INTO `D` VALUES (2,0,'c');
INSERT INTO `D` VALUES (3,0,'o');
INSERT INTO `D` VALUES (4,7,'c');
INSERT INTO `D` VALUES (5,8,'d');
INSERT INTO `D` VALUES (6,4,'v');
INSERT INTO `D` VALUES (7,6,'m');
INSERT INTO `D` VALUES (8,5,'j');
INSERT INTO `D` VALUES (9,NULL,'f');
INSERT INTO `D` VALUES (10,NULL,'n');
INSERT INTO `D` VALUES (11,8,'z');
INSERT INTO `D` VALUES (12,8,'h');
INSERT INTO `D` VALUES (13,8,'q');
INSERT INTO `D` VALUES (14,1,'w');
INSERT INTO `D` VALUES (15,1,'z');
INSERT INTO `D` VALUES (16,5,'j');
INSERT INTO `D` VALUES (17,2,'a');
INSERT INTO `D` VALUES (18,7,'m');
INSERT INTO `D` VALUES (19,6,'n');
INSERT INTO `D` VALUES (20,4,'e');
INSERT INTO `D` VALUES (21,7,'u');
INSERT INTO `D` VALUES (22,0,'s');
INSERT INTO `D` VALUES (23,9,'u');
INSERT INTO `D` VALUES (24,3,'r');
INSERT INTO `D` VALUES (25,5,'g');
INSERT INTO `D` VALUES (26,1,'o');
INSERT INTO `D` VALUES (27,1,'w');
INSERT INTO `D` VALUES (28,5,'b');
INSERT INTO `D` VALUES (29,9,NULL);
INSERT INTO `D` VALUES (30,2,'y');
INSERT INTO `D` VALUES (31,5,'y');
INSERT INTO `D` VALUES (32,248,'u');
INSERT INTO `D` VALUES (33,0,'p');
INSERT INTO `D` VALUES (34,8,'s');
INSERT INTO `D` VALUES (35,1,'e');
INSERT INTO `D` VALUES (36,255,'d');
INSERT INTO `D` VALUES (37,9,'d');
INSERT INTO `D` VALUES (38,9,'c');
INSERT INTO `D` VALUES (39,3,'b');
INSERT INTO `D` VALUES (40,9,'t');
INSERT INTO `D` VALUES (41,6,NULL);
INSERT INTO `D` VALUES (42,4,'y');
INSERT INTO `D` VALUES (43,60,'c');
INSERT INTO `D` VALUES (44,7,'d');
INSERT INTO `D` VALUES (45,1,'x');
INSERT INTO `D` VALUES (46,6,'p');
INSERT INTO `D` VALUES (47,4,'e');
INSERT INTO `D` VALUES (48,NULL,'g');
INSERT INTO `D` VALUES (49,8,'x');
INSERT INTO `D` VALUES (50,0,'s');
INSERT INTO `D` VALUES (51,8,'e');
INSERT INTO `D` VALUES (52,151,'l');
INSERT INTO `D` VALUES (53,7,'p');
INSERT INTO `D` VALUES (54,6,'h');
INSERT INTO `D` VALUES (55,NULL,'m');
INSERT INTO `D` VALUES (56,23,'n');
INSERT INTO `D` VALUES (57,2,'v');
INSERT INTO `D` VALUES (58,4,'b');
INSERT INTO `D` VALUES (59,NULL,'x');
INSERT INTO `D` VALUES (60,NULL,'r');
INSERT INTO `D` VALUES (61,77,'t');
INSERT INTO `D` VALUES (62,NULL,'w');
INSERT INTO `D` VALUES (63,NULL,'w');
INSERT INTO `D` VALUES (64,7,'k');
INSERT INTO `D` VALUES (65,1,'a');
INSERT INTO `D` VALUES (66,9,'t');
INSERT INTO `D` VALUES (67,6,'z');
INSERT INTO `D` VALUES (68,2,'e');
INSERT INTO `D` VALUES (69,3,'q');
INSERT INTO `D` VALUES (70,0,'e');
INSERT INTO `D` VALUES (71,NULL,'v');
INSERT INTO `D` VALUES (72,6,'d');
INSERT INTO `D` VALUES (73,3,'u');
INSERT INTO `D` VALUES (74,195,'o');
INSERT INTO `D` VALUES (75,5,'b');
INSERT INTO `D` VALUES (76,2,'c');
INSERT INTO `D` VALUES (77,7,'q');
INSERT INTO `D` VALUES (78,25,NULL);
INSERT INTO `D` VALUES (79,NULL,'h');
INSERT INTO `D` VALUES (80,0,'d');
INSERT INTO `D` VALUES (81,98,'w');
INSERT INTO `D` VALUES (82,6,'m');
INSERT INTO `D` VALUES (83,5,'i');
INSERT INTO `D` VALUES (84,0,'w');
INSERT INTO `D` VALUES (85,3,'f');
INSERT INTO `D` VALUES (86,1,'k');
INSERT INTO `D` VALUES (87,1,'v');
INSERT INTO `D` VALUES (88,147,'c');
INSERT INTO `D` VALUES (89,3,'y');
INSERT INTO `D` VALUES (90,3,'h');
INSERT INTO `D` VALUES (91,NULL,NULL);
INSERT INTO `D` VALUES (92,2,'t');
INSERT INTO `D` VALUES (93,1,'l');
INSERT INTO `D` VALUES (94,8,'a');
INSERT INTO `D` VALUES (95,8,'r');
INSERT INTO `D` VALUES (96,8,'s');
INSERT INTO `D` VALUES (97,0,'z');
INSERT INTO `D` VALUES (98,1,'j');
INSERT INTO `D` VALUES (99,8,'c');
INSERT INTO `D` VALUES (100,5,'f');

 
SELECT `col_varchar_key`  ,  MAX( `col_varchar_key`  )  
FROM D  
HAVING (  1  ,  2  )  IN (  
SELECT `pk`  , `col_int_key`  
FROM CC  
HAVING `col_int_key`  )   ;

DROP TABLE CC;
DROP TABLE D;
#/* End of test case for query 0 */

Full MTR test case with original, unsimplified query + simplified case

Attachment: bug52337_test.txt (text/plain), 13.35 KiB.

Full crash output

Attachment: bug52337_backtrace.txt (text/plain), 7.11 KiB.

Appears similar to:
Bug#46811	Crash - Assertion failed: (table->key_read == 0), function close_thread_table

Fix for Bug#52336 - Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355 also corrects this issue.