Bug #46860 Crash / segfault using EXPLAIN EXTENDED on query using UNION in subquery
Submitted: 21 Aug 2009 19:25 Modified: 14 Dec 2010 23:37
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4/6.0 OS:Any
Assigned to: Tor Didriksen CPU Architecture:Any
Tags: crashing bug, explain extended, regression, segfault, subquery, UNION

[21 Aug 2009 19:25] Patrick Crews
Description:
Azalea is crashing with a segmentation fault when it is issued a query using EXPLAIN EXTENDED and contains a UNION in a subquery:

The segfault is hit here:   Item_subselect::print (this=0x131a8c8, str=0xb051169c, query_type=QT_ORDINARY) at item_subselect.cc:383

This does not seem to be occurring in 5.1, and does not seem to be affected by optimizer_switch, optimizer_use_mrr, engine_condition_pushdown, or join_cache_level settings.

From the test case:
 EXPLAIN  EXTENDED  
SELECT table1 .`int_nokey`  
FROM CC table1  JOIN D table3  ON `varchar_key`  
WHERE table3 .`varchar_key`  >  ANY ( (  
SELECT `varchar_key`  
FROM BB  )  UNION (  
SELECT `varchar_key`  )  )   ;

Results in the following crash output (full output attached as separate file)
# 14:15:43 Thread 11 (core thread 10):
# 14:15:43 #0  0x90f03402 in __assert_rtn ()
# 14:15:43 #1  0x005abc7c in my_write_core (sig=10) at stacktrace.c:309
# 14:15:43 #2  0x000fd260 in handle_segfault (sig=10) at mysqld.cc:2738
# 14:15:43 #3  <signal handler called>
# 14:15:43 #4  0x000a755d in Item_subselect::print (this=0x131a8c8, str=0xb051169c, query_type=QT_ORDINARY) at item_subselect.cc:383
# 14:15:43 #5  0x000ac242 in Item_allany_subselect::print (this=0x131a8c8, str=0xb051169c, query_type=QT_ORDINARY) at item_subselect.cc:1982
# 14:15:43 #6  0x0004883f in Item_func::print_args (this=0x131a9c0, str=0xb051169c, from=0, query_type=QT_ORDINARY) at item_func.cc:418
# 14:15:43 #7  0x000488af in Item_func::print (this=0x131a9c0, str=0xb051169c, query_type=QT_ORDINARY) at item_func.cc:407
# 14:15:43 #8  0x0006205d in Item_func_not_all::print (this=0x131a9c0, str=0xb051169c, query_type=QT_ORDINARY) at item_cmpfunc.cc:327
# 14:15:43 #9  0x0006e5c9 in Item_cond::print (this=0x1347a28, str=0xb051169c, query_type=QT_ORDINARY) at item_cmpfunc.cc:4220
# 14:15:43 #10 0x001bdb0d in st_select_lex::print (this=0x124c61c, thd=0x124b218, str=0xb051169c, query_type=QT_ORDINARY) at sql_select.cc:22175
# 14:15:43 #11 0x000066a6 in st_select_lex_unit::print (this=0x124c16c, str=0xb051169c, query_type=QT_ORDINARY) at sql_lex.cc:2088
# 14:15:43 #12 0x0010fafc in execute_sqlcom_select (thd=0x124b218, all_tables=0x12523e8) at sql_parse.cc:4914
# 14:15:43 #13 0x001160ee in mysql_execute_command (thd=0x124b218) at sql_parse.cc:2112
# 14:15:43 #14 0x0011fbd7 in mysql_parse (thd=0x124b218, inBuf=0x1251628 "EXPLAIN EXTENDED SELECT    ( ( table2 . `pk` ) - ( table1 . `int_nokey` ) ) AS field1 FROM ( CC AS table1 INNER JOIN ( ( C AS table2 RIGHT OUTER JOIN D AS table3 ON (table3 . `pk` = table2 . `pk`  ) )"..., length=867, found_semicolon=0xb0512e14) at sql_parse.cc:5945
# 14:15:43 #15 0x00120754 in dispatch_command (command=COM_QUERY, thd=0x124b218, packet=0x19f0019 "", packet_length=869) at sql_parse.cc:1062
# 14:15:43 #16 0x00121b2a in do_command (thd=0x124b218) at sql_parse.cc:744
# 14:15:43 #17 0x0010d589 in handle_one_connection (arg=0x124b218) at sql_connect.cc:1163
# 14:15:43 #18 0x90e53155 in _pthread_start ()
# 14:15:43 #19 0x90e53012 in thread_start ()

How to repeat:
MTR test case:

/*!50400 SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=off,semijoin=off' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;

#/* Begin test case for query 0 */

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

CREATE TABLE `CC` (
  `int_nokey` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (7,'v'),(1,'r'),(5,'a'),(3,'m'),(6,'y'),(92,'j'),(7,'d'),(NULL,'z'),(3,'e'),(5,'h'),(1,'b'),(2,'s'),(NULL,'e'),(1,'j'),(0,'e'),(210,'f'),(8,'v'),(7,'x'),(5,'m'),(NULL,'c');
CREATE TABLE `D` (
  `int_nokey` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (6,'r'),(8,'c'),(6,'o'),(6,'c'),(3,'d'),(9,'v'),(2,'m'),(1,'j'),(8,'f'),(0,'n'),(9,'z'),(8,'h'),(NULL,'q'),(0,'w'),(5,'z'),(1,'j'),(1,'a'),(6,'m'),(6,'n'),(1,'e'),(8,'u'),(1,'s'),(0,'u'),(4,'r'),(9,'g'),(8,'o'),(5,'w'),(9,'b'),(5,NULL),(NULL,'y'),(NULL,'y'),(105,'u'),(0,'p'),(3,'s'),(1,'e'),(75,'d'),(9,'d'),(7,'c'),(NULL,'b'),(NULL,'t'),(4,NULL),(0,'y'),(204,'c'),(0,'d'),(9,'x'),(8,'p'),(7,'e'),(8,'g'),(NULL,'x'),(6,'s'),(5,'e'),(2,'l'),(3,'p'),(7,'h'),(NULL,'m'),(145,'n'),(0,'v'),(1,'b'),(7,'x'),(3,'r'),(NULL,'t'),(2,'w'),(2,'w'),(2,'k'),(8,'a'),(6,'t'),(1,'z'),(NULL,'e'),(1,'q'),(0,'e'),(4,'v'),(1,'d'),(1,'u'),(27,'o'),(4,'b'),(6,'c'),(2,'q'),(248,NULL),(NULL,'h'),(9,'d'),(75,'w'),(2,'m'),(9,'i'),(4,'w'),(0,'f'),(0,'k'),(1,'v'),(119,'c'),(1,'y'),(7,'h'),(2,NULL),(7,'t'),(2,'l'),(6,'a'),(4,'r'),(5,'s'),(7,'z'),(1,'j'),(7,'c'),(2,'f');
CREATE TABLE `BB` (
  `int_nokey` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (8,NULL);

 EXPLAIN  EXTENDED  
SELECT table1 .`int_nokey`  
FROM CC table1  JOIN D table3  ON `varchar_key`  
WHERE table3 .`varchar_key`  >  ANY ( (  
SELECT `varchar_key`  
FROM BB  )  UNION (  
SELECT `varchar_key`  )  )   ;

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

 EXPLAIN EXTENDED 
SELECT    ( ( table2 . `pk` ) - ( table1 . `int_nokey` ) ) AS field1 
FROM ( CC AS table1 INNER JOIN ( ( C AS table2 RIGHT OUTER JOIN D AS table3 ON (table3 . `pk` = table2 . `pk`  ) ) ) ON (table3 . `varchar_nokey` = table2 . `varchar_key`  ) ) 
WHERE (  table3 . `varchar_key` > ANY ( ( 
SELECT   SUBQUERY1_t2 . `varchar_key` AS SUBQUERY1_field1 
FROM ( CC AS SUBQUERY1_t1 STRAIGHT_JOIN ( ( BB AS SUBQUERY1_t2 LEFT  JOIN CC AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `varchar_key` = SUBQUERY1_t2 . `varchar_nokey`  ) ) ) ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `pk`  ) )   ) UNION  ( 
SELECT   SUBQUERY1_t1 . `varchar_key` AS SUBQUERY1_field1 
FROM ( CC AS SUBQUERY1_t4 STRAIGHT_JOIN C AS SUBQUERY1_t5 ON (SUBQUERY1_t5 . `varchar_key` = SUBQUERY1_t4 . `varchar_nokey`  ) )   ) ) ) OR table1 . `varchar_key` IS  NULL  
GROUP BY field1  
ORDER BY field1 ASC, field1;

DROP TABLE CC;
DROP TABLE D;
DROP TABLE C;
DROP TABLE BB;
#/* End of test case for query 1 */

Suggested fix:
Ensure crash-free query processing.
[21 Aug 2009 19:27] Patrick Crews
Full RQG-generated crash output

Attachment: bug46860_crash_output.txt (text/plain), 17.21 KiB.

[24 Aug 2009 12:51] MySQL Verification Team
simplified testcase query #0;

drop table if exists `t1`;
create table `t1` (`a` int) engine=myisam;
explain extended select 1 from `t1`,`t1` as `t2`
where `t1`.`a`  > all
(
        (select `a` from `t1` ) union (select `a`)
);
[21 Sep 2009 10:46] Bugs System
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/83886

2827 Tor Didriksen	2009-09-21
      Bug #46860 Crash/segfault using EXPLAIN EXTENDED on query using UNION in subquery
      
      EXPLAIN EXTENDED <query> will not generate a Note with extended information
      if EXPLAIN <query> returns an error.
     @ mysql-test/r/explain.result
        Add testcase based on bug report.
     @ mysql-test/r/subselect3.result
        Remove the extended Note, since explain returns error.
     @ mysql-test/r/subselect3_jcl6.result
        Remove the extended Note, since explain returns error.
     @ mysql-test/t/explain.test
        Add testcase based on bug report.
     @ sql/sql_parse.cc
        Skip the extended description if we have an error.
[21 Sep 2009 12:57] Bugs System
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/83930

2829 Tor Didriksen	2009-09-21
      Bug #46860 Crash/segfault using EXPLAIN EXTENDED on query using UNION in subquery
      
      EXPLAIN EXTENDED <query> will not generate a Note with extended information
      if EXPLAIN <query> returns an error.
     @ mysql-test/r/explain.result
        Add testcase based on bug report.
     @ mysql-test/r/subselect3.result
        Remove the extended Note, since explain returns error.
     @ mysql-test/r/subselect3_jcl6.result
        Remove the extended Note, since explain returns error.
     @ mysql-test/t/explain.test
        Add testcase based on bug report.
     @ sql/sql_parse.cc
        Skip the extended description if we have an error.
[21 Sep 2009 13:14] Tor Didriksen
bzr push into mysql-6.0-bugfixing
[30 Sep 2009 8:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)
[1 Oct 2009 17:47] Paul DuBois
Noted in 6.0.14 changelog.

EXPLAIN EXTENDED did not generate a Note with extended information if
the query was malformed. 

Setting report to NDI pending push into 5.4.x.
[1 Oct 2009 18:15] Guilhem Bichot
Actually, the bug was that a malformed query would make EXPLAIN EXTENDED crash when wanting to show the optimizer-transformed query.
Tor's fix is that if the query is malformed, EXPLAIN EXTENDED does not try to show the optimizer-transformed query at all (avoids the crash).
[8 Oct 2009 0:33] Paul DuBois
Revised changelog entry:

A malformed query caused EXPLAIN EXTENDED to crash during the attempt
to display the the optimizer-transformed query. Now if the query is
malformed, EXPLAIN EXTENDED does not try to show the
optimizer-transformed query.  

Setting report to NDI pending push into 5.4.x.
[13 Apr 2010 11:58] Bugs System
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/105496

3071 Tor Didriksen	2010-04-13
      Bug #46860 Crash/segfault using EXPLAIN EXTENDED on query using UNION in subquery
      
      Backport of tor.didriksen@sun.com-20090921125638-vwidsxe77gf16qkl
      
      EXPLAIN EXTENDED <query> will not generate a Note with extended information
      if EXPLAIN <query> returns an error.
     @ mysql-test/r/explain.result
        Add testcase based on bug report.
     @ mysql-test/r/subselect3.result
        Remove the extended Note, since explain returns error.
     @ mysql-test/t/explain.test
        Add testcase based on bug report.
     @ sql/sql_parse.cc
        Skip the extended description if we have an error.
[16 Aug 2010 6:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:07] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 0:51] Paul DuBois
Noted in 5.6.1 changelog.
[14 Dec 2010 23:37] Paul DuBois
Correction: No 5.6.1 changelog entry needed.