Description:
The following query (using EXPLAIN) causes a crash / segfault in 5.1 and 5.4/6.0 (not tested against 5.0).
Without the EXPLAIN clause, the query will process without incident.
This crash occurs in the same are noted in this bug:
Bug#46749: Segfault in add_key_fields() with outer subquery level field references
However, the fix for this bug should be present in the 5.1-bugteam tree I am using. Writing this up after conversation with assigned developer.
EXPLAIN
SELECT (
SELECT SUBQUERY1_t1 .`int_key`
FROM CC SUBQUERY1_t1 STRAIGHT_JOIN CC SUBQUERY1_t2 ON SUBQUERY1_t1 .`pk`
WHERE table1 .`varchar_nokey` OR SUBQUERY1_t2 .`varchar_key` > table1 .`varchar_key` ) , table2 .`date_key` field3
FROM C table1 JOIN BB table2 ON table1 .`varchar_key`
WHERE NULL
GROUP BY field3 ;
Partial crash output (full text attached as separate file):
# 13:08:46 Thread 11 (core thread 10):
# 13:08:46 #0 0x9510a402 in __assert_rtn ()
# 13:08:46 #1 0x0056ef60 in my_write_core (sig=10) at stacktrace.c:310
# 13:08:46 #2 0x000f9549 in handle_segfault (sig=10) at mysqld.cc:2552
# 13:08:46 #3 <signal handler called>
# 13:08:46 #4 0x0016bdf9 in Bitmap<64u>::merge (this=0xc0, map2=@0xb041fe04) at sql_bitmap.h:129
# 13:08:46 #5 0x0017bef7 in add_key_field (key_fields=0xb04202d0, and_level=2, cond=0x1087570, field=0x108dda8, eq_func=false, value=0x10875c4, num_values=1, usable_tables=18446744073709551615, sargables=0xb04205c8) at sql_select.cc:3141
# 13:08:46 #6 0x0017c2b1 in add_key_equal_fields (key_fields=0xb04202d0, and_level=2, cond=0x1087570, field_item=0x10874e0, eq_func=false, val=0x10875c4, num_values=1, usable_tables=18446744073709551615, sargables=0xb04205c8) at sql_select.cc:3280
# 13:08:46 #7 0x0017cccf in add_key_fields (join=0x10a8c28, key_fields=0xb04202d0, and_level=0xb04202d4, cond=0x1087570, usable_tables=18446744073709551615, sargables=0xb04205c8) at sql_select.cc:3451
# 13:08:46 #8 0x0017c5b8 in add_key_fields (join=0x10a8c28, key_fields=0xb04202d0, and_level=0xb04202d4, cond=0x1087660, usable_tables=18446744073709551615, sargables=0xb04205c8) at sql_select.cc:3354
# 13:08:46 #9 0x0017c515 in add_key_fields (join=0x10a8c28, key_fields=0xb04202d0, and_level=0xb04202d4, cond=0x10a87b8, usable_tables=18446744073709551615, sargables=0xb04205c8) at sql_select.cc:3339
# 13:08:46 #10 0x0017dc45 in update_ref_and_keys (thd=0x1084418, keyuse=0x10a9d2c, join_tab=0x10a9e08, tables=2, cond=0x10a87b8, cond_equal=0x10a8848, normal_tables=18446744073709551615, select_lex=0x10866f0, sargables=0xb04205c8) at sql_select.cc:3786
# 13:08:46 #11 0x0018fd0d in make_join_statistics (join=0x10a8c28, tables_arg=0x1086bf0, conds=0x10a87b8, keyuse_array=0x10a9d2c) at sql_select.cc:2631
# 13:08:46 #12 0x0019f1ba in JOIN::optimize (this=0x10a8c28) at sql_select.cc:954
# 13:08:46 #13 0x001a6f2f in mysql_select (thd=0x1084418, rref_pointer_array=0x10867e8, tables=0x1086bf0, wild_num=0, fields=@0x1086784, conds=0x1087660, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x1088280, unit=0x1086880, select_lex=0x10866f0) at sql_select.cc:2384
# 13:08:46 #14 0x001a1ae9 in mysql_explain_union (thd=0x1084418, unit=0x1086880, result=0x1088280) at sql_select.cc:16421
# 13:08:46 #15 0x001a44b1 in select_describe (join=0x10a6e58, need_tmp_table=false, need_order=false, distinct=false, message=0x613165 "Impossible WHERE") at sql_select.cc:16362
# 13:08:46 #16 0x001a45ed in return_zero_rows (join=0x10a6e58, result=0x1088280, tables=0x10879e0, fields=@0x1085800, send_row=false, select_options=2147764740, info=0x613165 "Impossible WHERE", having=0x0) at sql_select.cc:7107
# 13:08:46 #17 0x001a4e05 in JOIN::exec (this=0x10a6e58) at sql_select.cc:1725
# 13:08:46 #18 0x001a6fbf in mysql_select (thd=0x1084418, rref_pointer_array=0x1085864, tables=0x10879e0, wild_num=0, fields=@0x1085800, conds=0x1088100, og_num=1, order=0x0, group=0x10881e8, having=0x0, proc_param=0x0, select_options=2147764740, result=0x1088280, unit=0x10854fc, select_lex=0x108576c) at sql_select.cc:2398
# 13:08:46 #19 0x001a1ae9 in mysql_explain_union (thd=0x1084418, unit=0x10854fc, result=0x1088280) at sql_select.cc:16421
# 13:08:46 #20 0x0010ae24 in execute_sqlcom_select (thd=0x1084418, all_tables=0x10879e0) at sql_parse.cc:4989
# 13:08:46 #21 0x00111a40 in mysql_execute_command (thd=0x1084418) at sql_parse.cc:2206
# 13:08:46 #22 0x0011bacb in mysql_parse (thd=0x1084418, inBuf=0x1086428 "EXPLAIN SELECT ( SELECT SUBQUERY1_t1 .`int_key` FROM CC SUBQUERY1_t1 STRAIGHT_JOIN CC SUBQUERY1_t2 ON SUBQUERY1_t1 .`pk` WHERE table1 .`varchar_nokey` OR SUBQUERY1_t2 .`varchar_key` > table1 .`varchar"..., length=316, found_semicolon=0xb0422df0) at sql_parse.cc:5931
# 13:08:46 #23 0x0011c856 in dispatch_command (command=COM_QUERY, thd=0x1084418, packet=0x19c8019 "", packet_length=316) at sql_parse.cc:1213
# 13:08:46 #24 0x0011db6b in do_command (thd=0x1084418) at sql_parse.cc:854
# 13:08:46 #25 0x00108c67 in handle_one_connection (arg=0x1084418) at sql_connect.cc:1127
# 13:08:46 #26 0x9505a155 in _pthread_start ()
# 13:08:46 #27 0x9505a012 in thread_start ()
How to repeat:
MTR test case (this was used on 5.1-bugteam)
Observe how the removal of the EXPLAIN will cause the crash to vanish.
/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */;
/*!50400 SET SESSION optimizer_use_mrr = '' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = '' */;
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
--enable_warnings
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_key` int(11) DEFAULT NULL,
`date_key` date DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,NULL,'v','v'),(11,9,'2006-06-14','r','r'),(12,9,'2002-09-12','a','a'),(13,186,'2005-02-15','m','m'),(14,NULL,NULL,'y','y'),(15,2,'2008-11-04','j','j'),(16,3,'2004-09-04','d','d'),(17,0,'2006-06-05','z','z'),(18,133,'1900-01-01','e','e'),(19,1,'1900-01-01','h','h'),(20,8,'1900-01-01','b','b'),(21,5,'2005-01-13','s','s'),(22,5,'2006-05-21','e','e'),(23,8,'2003-09-08','j','j'),(24,6,'2006-12-23','e','e'),(25,51,'2006-10-15','f','f'),(26,4,'2005-04-06','v','v'),(27,7,'2008-04-07','x','x'),(28,6,'2006-10-10','m','m'),(29,4,'1900-01-01','c','c');
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_key` int(11) DEFAULT NULL,
`date_key` date DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,NULL,'w','w'),(2,9,'2001-09-19','m','m'),(3,3,'2004-09-12','m','m'),(4,9,NULL,'k','k'),(5,NULL,'2002-07-19','r','r'),(6,9,'2002-12-16','t','t'),(7,3,'2006-02-08','j','j'),(8,8,'2006-08-28','u','u'),(9,8,'2001-04-14','h','h'),(10,53,'2000-01-05','o','o'),(11,0,'2003-12-06',NULL,NULL),(12,5,'1900-01-01','k','k'),(13,166,'2002-11-27','e','e'),(14,3,NULL,'n','n'),(15,0,'2003-05-27','t','t'),(16,1,'2005-05-03','c','c'),(17,9,'2001-04-18','m','m'),(18,5,'2005-12-27','y','y'),(19,6,'2004-08-20','f','f'),(20,2,'1900-01-01','d','d');
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_key` int(11) DEFAULT NULL,
`date_key` date DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,'2002-02-21',NULL,NULL);
EXPLAIN
SELECT (
SELECT SUBQUERY1_t1 .`int_key`
FROM CC SUBQUERY1_t1 STRAIGHT_JOIN CC SUBQUERY1_t2 ON SUBQUERY1_t1 .`pk`
WHERE table1 .`varchar_nokey` OR SUBQUERY1_t2 .`varchar_key` > table1 .`varchar_key` ) , table2 .`date_key` field3
FROM C table1 JOIN BB table2 ON table1 .`varchar_key`
WHERE NULL
GROUP BY field3 ;
DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;
#/* End of test case for query 0 */
#/* Begin test case for query 1 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
--enable_warnings
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`int_key` int(11) DEFAULT NULL,
`date_key` date DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,NULL,'v','v'),(11,1,9,'2006-06-14','r','r'),(12,5,9,'2002-09-12','a','a'),(13,3,186,'2005-02-15','m','m'),(14,6,NULL,NULL,'y','y'),(15,92,2,'2008-11-04','j','j'),(16,7,3,'2004-09-04','d','d'),(17,NULL,0,'2006-06-05','z','z'),(18,3,133,'1900-01-01','e','e'),(19,5,1,'1900-01-01','h','h'),(20,1,8,'1900-01-01','b','b'),(21,2,5,'2005-01-13','s','s'),(22,NULL,5,'2006-05-21','e','e'),(23,1,8,'2003-09-08','j','j'),(24,0,6,'2006-12-23','e','e'),(25,210,51,'2006-10-15','f','f'),(26,8,4,'2005-04-06','v','v'),(27,7,7,'2008-04-07','x','x'),(28,5,6,'2006-10-10','m','m'),(29,NULL,4,'1900-01-01','c','c');
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`int_key` int(11) DEFAULT NULL,
`date_key` date DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,NULL,'w','w'),(2,7,9,'2001-09-19','m','m'),(3,9,3,'2004-09-12','m','m'),(4,7,9,NULL,'k','k'),(5,4,NULL,'2002-07-19','r','r'),(6,2,9,'2002-12-16','t','t'),(7,6,3,'2006-02-08','j','j'),(8,8,8,'2006-08-28','u','u'),(9,NULL,8,'2001-04-14','h','h'),(10,5,53,'2000-01-05','o','o'),(11,NULL,0,'2003-12-06',NULL,NULL),(12,6,5,'1900-01-01','k','k'),(13,188,166,'2002-11-27','e','e'),(14,2,3,NULL,'n','n'),(15,1,0,'2003-05-27','t','t'),(16,1,1,'2005-05-03','c','c'),(17,0,9,'2001-04-18','m','m'),(18,9,5,'2005-12-27','y','y'),(19,NULL,6,'2004-08-20','f','f'),(20,4,2,'1900-01-01','d','d');
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`int_key` int(11) DEFAULT NULL,
`date_key` date DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,8,'2002-02-21',NULL,NULL);
EXPLAIN
SELECT (
SELECT MIN( SUBQUERY1_t1 . `int_key` ) AS SUBQUERY1_field1
FROM ( CC AS SUBQUERY1_t1 STRAIGHT_JOIN CC AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `pk` = SUBQUERY1_t1 . `pk` ) )
WHERE SUBQUERY1_t2 . `varchar_key` <> table1 . `varchar_nokey` OR SUBQUERY1_t2 . `varchar_key` > table1 . `varchar_key` ) AS field1 , table2 . `int_key` AS field2 , table2 . `date_key` AS field3
FROM ( C AS table1 LEFT JOIN BB AS table2 ON (table2 . `varchar_key` = table1 . `varchar_key` ) )
WHERE ( (
SELECT SQL_SMALL_RESULT SUM( SUBQUERY2_t2 . `int_key` ) AS SUBQUERY2_field1
FROM ( CC AS SUBQUERY2_t1 INNER JOIN CC AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `varchar_key` = SUBQUERY2_t1 . `varchar_key` ) )
WHERE ( SUBQUERY2_t2 . `int_nokey` > ANY (
SELECT CHILD_SUBQUERY1_t2 . `pk` AS CHILD_SUBQUERY2_field1
FROM ( C AS CHILD_SUBQUERY1_t1 RIGHT JOIN C AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `pk` = CHILD_SUBQUERY1_t1 . `pk` ) )
WHERE CHILD_SUBQUERY1_t2 . `int_key` >= SUBQUERY2_t1 . `int_key` ) OR SUBQUERY2_t1 . `varchar_nokey` <> SUBQUERY2_t1 . `varchar_key` ) ) IS NULL ) AND ( table1 . `pk` > 192 AND table1 . `pk` < ( 192 + 66 ) OR table1 . `int_key` > 192 AND table1 . `int_key` < ( 192 + 11 ) )
GROUP BY field1, field2, field3
ORDER BY field3 DESC , table1 . `varchar_key` , table1 .`pk` ASC , table1 . `varchar_key` , table1 .`pk` DESC , field2 ASC , field1 DESC;
DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;
#/* End of test case for query 1 */
Suggested fix:
Ensure crash-free query processing.
