diff options
Diffstat (limited to 'mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test')
| -rw-r--r-- | mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test | 183 |
1 files changed, 183 insertions, 0 deletions
diff --git a/mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test b/mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test new file mode 100644 index 00000000..85e24824 --- /dev/null +++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test @@ -0,0 +1,183 @@ +# BUG#45219 "Azalea crash on query containing a JOIN in subquery" + +# Both SELECT DISTINCT cause similar crashes. +# With ./mtr --mysqld=--optimizer_switch="semijoin=off" optimizer_unfixed_bugs.bug45219 +# there is no crash, the result file has been created with this +# command, but it has *not* been checked for correctness of returned +# data (at least 5.1 gives the same result). + + +# 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 |