RQG: close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed with subquery cache

[Philip Stoev]

Immediately after executing the following statement with subquery cache enabled:

SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM B SUBQUERY2_t1 JOIN CC ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
FROM D table1 JOIN ( C table2 STRAIGHT_JOIN C table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
GROUP BY field10 ;

the server asserts as follows:

mysqld: sql_base.cc:1380: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed.

#8 0x00897de8 in __assert_fail () from /lib/libc.so.6
#9 0x082dd22f in close_thread_table (thd=0xae6ee90, table_ptr=0xae6eedc) at sql_base.cc:1380
#10 0x082dcd7b in close_open_tables (thd=0xae6ee90) at sql_base.cc:1203
#11 0x082dd0db in close_thread_tables (thd=0xae6ee90) at sql_base.cc:1355
#12 0x0828fb23 in dispatch_command (command=COM_QUERY, thd=0xae6ee90, packet=0xae87271 "", packet_length=379) at sql_parse.cc:1613
#13 0x0828dea8 in do_command (thd=0xae6ee90) at sql_parse.cc:890
#14 0x0828b008 in handle_one_connection (arg=0xae6ee90) at sql_connect.cc:1153
#15 0x00a08919 in start_thread () from /lib/libpthread.so.0
#16 0x00951e5e in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Apologies for the long test case, but automatic simplification was not possible

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

INSERT INTO `C` VALUES (1,NULL,2,NULL,NULL,'11:28:45','11:28:45','2004-10-11 18:13:16','2004-10-11 18:13:16','w','w');
INSERT INTO `C` VALUES (2,7,9,'2001-09-19','2001-09-19','20:25:14','20:25:14',NULL,NULL,'m','m');
INSERT INTO `C` VALUES (3,9,3,'2004-09-12','2004-09-12','13:47:24','13:47:24','1900-01-01 00:00:00','1900-01-01 00:00:00','m','m');
INSERT INTO `C` VALUES (4,7,9,NULL,NULL,'19:24:11','19:24:11','2009-07-25 00:00:00','2009-07-25 00:00:00','k','k');
INSERT INTO `C` VALUES (5,4,NULL,'2002-07-19','2002-07-19','15:59:13','15:59:13',NULL,NULL,'r','r');
INSERT INTO `C` VALUES (6,2,9,'2002-12-16','2002-12-16','00:00:00','00:00:00','2008-07-27 00:00:00','2008-07-27 00:00:00','t','t');
INSERT INTO `C` VALUES (7,6,3,'2006-02-08','2006-02-08','15:15:04','15:15:04','2002-11-13 16:37:31','2002-11-13 16:37:31','j','j');
INSERT INTO `C` VALUES (8,8,8,'2006-08-28','2006-08-28','11:32:06','11:32:06','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u');
INSERT INTO `C` VALUES (9,NULL,8,'2001-04-14','2001-04-14','18:32:33','18:32:33','2003-12-10 00:00:00','2003-12-10 00:00:00','h','h');
INSERT INTO `C` VALUES (10,5,53,'2000-01-05','2000-01-05','15:19:25','15:19:25','2001-12-21 22:38:22','2001-12-21 22:38:22','o','o');
INSERT INTO `C` VALUES (11,NULL,0,'2003-12-06','2003-12-06','19:03:19','19:03:19','2008-12-13 23:16:44','2008-12-13 23:16:44',NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'1900-01-01','1900-01-01','00:39:46','00:39:46','2005-08-15 12:39:41','2005-08-15 12:39:41','k','k');
INSERT INTO `C` VALUES (13,188,166,'2002-11-27','2002-11-27',NULL,NULL,NULL,NULL,'e','e');
INSERT INTO `C` VALUES (14,2,3,NULL,NULL,'00:00:00','00:00:00','2006-09-11 12:06:14','2006-09-11 12:06:14','n','n');
INSERT INTO `C` VALUES (15,1,0,'2003-05-27','2003-05-27','13:12:11','13:12:11','2007-12-15 12:39:34','2007-12-15 12:39:34','t','t');
INSERT INTO `C` VALUES (16,1,1,'2005-05-03','2005-05-03','04:56:48','04:56:48','2005-08-09 00:00:00','2005-08-09 00:00:00','c','c');
INSERT INTO `C` VALUES (17,0,9,'2001-04-18','2001-04-18','19:56:05','19:56:05','2001-09-02 22:50:02','2001-09-02 22:50:02','m','m');
INSERT INTO `C` VALUES (18,9,5,'2005-12-27','2005-12-27','19:35:19','19:35:19','2005-12-16 22:58:11','2005-12-16 22:58:11','y','y');
INSERT INTO `C` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007...