MySQL Bugs: #45219: Crash on SELECT DISTINCT query containing a LEFT JOIN in subquery

Bug #45219	Crash on SELECT DISTINCT query containing a LEFT JOIN in subquery
Submitted:	31 May 2009 17:51	Modified:	13 Nov 2009 11:52
Reporter:	Patrick Crews	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	optimizer_switch, outerjoin, semijoin, subquery

Description:
The following query generated by the RQG grammar file subquery_semijoin.yy produced a crash in 5.4, during a comparison between 5.1-bugteam and 5.4

SELECT DISTINCT BIT_AND( OUTR . `datetime_key` ) AS X FROM C AS OUTR WHERE ( OUTR . `int_nokey` , OUTR . `pk` ) IN ( SELECT DISTINCT INNR . `pk` AS X , INNR . `pk` AS Y FROM CC AS INNR2 LEFT JOIN BB AS INNR ON ( INNR2 . `varchar_nokey` = INNR . `varchar_key` ) WHERE INNR . `date_key` BETWEEN '2009-04-26' AND '2004-08-21'  ) AND OUTR . `pk` = 9  ORDER BY OUTR . `int_key` , OUTR . `pk` failed: 2013 Lost connection to MySQL server during query

This has only occurred once for me.  

I have attached the failure output.  No trace files are available as this occurred during a test run of the RQG and the crash took me by surprise.  Log files are available as needed.

How to repeat:
perl ./runall.pl --basedir1=<path>/mysql-5.1-bugteam --basedir2=<path>/mysql-azalea --grammar=subquery_semijoin.yy

NOTE:  I didn't use threads=1 nor did I use an init-file to turn subquery optimizations off.

It should also be possible to alter a grammar file to use queries patterned more like the failing query to ensure higher repeat probability.

I have only seen this crash once so far.

Suggested fix:
Ensure the server doesn't crash in this fashion.

RQG Crash output including data from master.log files.

Attachment: azalea_rqg_crash_output.txt (text/plain), 8.32 KiB.

Simplified query:
SELECT `varchar_nokey`  FROM B  WHERE ( `pk`  , `pk`  )  IN (  SELECT PARENT1 .`int_nokey`  , PARENT1 .`pk`  FROM BB PARENT1  LEFT  JOIN BB PARENT2  ON PARENT2 .`varchar_nokey`  WHERE PARENT1 .`pk`  =  4  );

Still working on repeating this bug to produce a proper, RQG-generated test case.

NOTE:  This crash is caused by having the optimizer_switch value semijoin=on (turning this to off eliminates the crash)

/*!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=on,semijoin=on' */;
/*!50400 SET SESSION optimizer_use_mrr = 'disable' */;
/*!50400 SET SESSION engine_condition_pushdown = 0 */;
/*!50400 SET SESSION join_cache_level = 0 */;

# Begin test case for query 0

--disable_warnings
DROP TABLE IF EXISTS CC, C, BB;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,0,'2006-07-07 07:26:28','q'),(11,5,'2002-09-23 00:00:00','m'),(12,7,'0000-00-00 00:00:00','j'),(13,1,'2006-06-07 00:00:00','z'),(14,8,'2000-09-16 12:15:34','a'),(15,2,'2007-08-05 15:47:52',''),(16,1,'0000-00-00 00:00:00','e'),(17,8,'2005-12-02 19:34:26','t'),(18,5,'0000-00-00 00:00:00','q'),(19,4,'0000-00-00 00:00:00','b'),(20,5,'2007-12-28 00:00:00','w'),(21,3,'2004-08-02 11:48:43','m'),(22,0,'0000-00-00 00:00:00','x'),(23,8,'2004-04-19 12:18:43',''),(24,0,'2009-04-27 00:00:00','w'),(25,4,'2006-10-20 14:52:15','x'),(26,0,'0000-00-00 00:00:00','e'),(27,0,'2002-03-22 11:48:37','e'),(28,2,'0000-00-00 00:00:00','p'),(29,0,'2001-01-04 03:55:07','x');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,9,'0000-00-00 00:00:00','p'),(2,0,'2002-02-09 07:38:13','v'),(3,8,'2001-05-03 12:08:14','t'),(4,3,'0000-00-00 00:00:00','u'),(5,7,'2009-07-28 03:43:30','n'),(6,0,'2009-08-04 00:00:00','l'),(7,1,'0000-00-00 00:00:00','h'),(8,9,'0000-00-00 00:00:00','u'),(9,0,'2005-08-02 17:16:54','n'),(10,9,'2002-12-21 00:00:00','j'),(11,0,'2005-08-15 12:37:35','k'),(12,5,'0000-00-00 00:00:00','e'),(13,0,'2006-03-10 00:00:00','i'),(14,8,'2005-05-16 11:02:36','u'),(15,8,'2008-11-02 00:00:00','n'),(16,5,'2006-03-15 00:00:00','b'),(17,1,'0000-00-00 00:00:00','x'),(18,7,'0000-00-00 00:00:00',''),(19,0,'2008-12-17 20:15:40','q'),(20,9,'0000-00-00 00:00:00','u');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,'2007-08-19 08:08:38','i'),(11,0,'2000-05-21 03:51:51','');

 
SELECT  DISTINCT `datetime_key`  
FROM C  
WHERE ( `int_nokey`  , `pk`  )  IN (  
SELECT INNR .`pk`  , INNR .`pk`  
FROM CC  LEFT  JOIN BB INNR  ON INNR .`varchar_key`  )  AND `pk`  =  9   ;

DROP TABLE CC, C, BB;

# End of test case for query 0

# Begin test case for query 1

--disable_warnings
DROP TABLE IF EXISTS CC, C, BB;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,0,8,'2007-02-14','2006-07-07 07:26:28','q','q'),(11,5,8,'2002-10-03','2002-09-23 00:00:00','m','m'),(12,7,3,'2006-12-02','0000-00-00 00:00:00','j','j'),(13,1,2,'2007-05-02','2006-06-07 00:00:00','z','z'),(14,8,2,'2001-11-18','2000-09-16 12:15:34','a','a'),(15,2,6,'2006-09-09','2007-08-05 15:47:52','',''),(16,1,8,'0000-00-00','0000-00-00 00:00:00','e','e'),(17,8,9,'2003-07-22','2005-12-02 19:34:26','t','t'),(18,5,2,'2001-12-22','0000-00-00 00:00:00','q','q'),(19,4,6,'0000-00-00','0000-00-00 00:00:00','b','b'),(20,5,5,'2006-09-02','2007-12-28 00:00:00','w','w'),(21,3,2,'0000-00-00','2004-08-02 11:48:43','m','m'),(22,0,4,'0000-00-00','0000-00-00 00:00:00','x','x'),(23,8,9,'2001-02-28','2004-04-19 12:18:43','',''),(24,0,6,'0000-00-00','2009-04-27 00:00:00','w','w'),(25,4,5,'2007-05-19','2006-10-20 14:52:15','x','x'),(26,0,0,'2005-02-15','0000-00-00 00:00:00','e','e'),(27,0,0,'2000-10-19','2002-03-22 11:48:37','e','e'),(28,2,8,'2005-07-07','0000-00-00 00:00:00','p','p'),(29,0,0,'2008-10-18','2001-01-04 03:55:07','x','x');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,9,9,'2007-12-01','0000-00-00 00:00:00','p','p'),(2,0,0,'0000-00-00','2002-02-09 07:38:13','v','v'),(3,8,6,'0000-00-00','2001-05-03 12:08:14','t','t'),(4,3,6,'2002-05-07','0000-00-00 00:00:00','u','u'),(5,7,6,'0000-00-00','2009-07-28 03:43:30','n','n'),(6,0,4,'0000-00-00','2009-08-04 00:00:00','l','l'),(7,1,7,'0000-00-00','0000-00-00 00:00:00','h','h'),(8,9,4,'0000-00-00','0000-00-00 00:00:00','u','u'),(9,0,8,'0000-00-00','2005-08-02 17:16:54','n','n'),(10,9,4,'2000-12-18','2002-12-21 00:00:00','j','j'),(11,0,7,'2005-11-13','2005-08-15 12:37:35','k','k'),(12,5,5,'0000-00-00','0000-00-00 00:00:00','e','e'),(13,0,0,'2003-11-12','2006-03-10 00:00:00','i','i'),(14,8,5,'2006-02-20','2005-05-16 11:02:36','u','u'),(15,8,7,'2005-02-12','2008-11-02 00:00:00','n','n'),(16,5,2,'2009-07-20','2006-03-15 00:00:00','b','b'),(17,1,8,'2005-02-24','0000-00-00 00:00:00','x','x'),(18,7,0,'0000-00-00','0000-00-00 00:00:00','',''),(19,0,9,'0000-00-00','2008-12-17 20:15:40','q','q'),(20,9,5,'0000-00-00','0000-00-00 00:00:00','u','u');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,5,'0000-00-00','2007-08-19 08:08:38','i','i'),(11,0,8,'2005-08-18','2000-05-21 03:51:51','','');

SELECT DISTINCT BIT_AND( OUTR . `datetime_key` ) AS X 
FROM C AS OUTR 
WHERE ( OUTR . `int_nokey` , OUTR . `pk` ) IN ( 
SELECT DISTINCT INNR . `pk` AS X , INNR . `pk` AS Y 
FROM CC AS INNR2 LEFT JOIN BB AS INNR ON ( INNR2 . `varchar_nokey` = INNR . `varchar_key` ) 
WHERE INNR . `date_key` BETWEEN '2009-04-26' AND '2004-08-21'  ) AND OUTR . `pk` = 9 
ORDER BY OUTR . `int_key` , OUTR . `pk`;

DROP TABLE CC, C, BB;

# End of test case for query 1

testcase for this bug has been added to 6.0-code-base-bugfixing/mysql-test/suite/optimizer_unfixed_bugs/*/bug45219.*

This is a duplicate of Bug#46692.  It has been verified that a fix for that bug fixes this issue too.