|
Re: mysqld: sql_select.cc:12918: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed in maria-5.3-mwl89
Test case. Maria-5.3 is not affected.
SET SESSION optimizer_switch='semijoin=off,materialization=off';
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time 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_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,'01:27:35','v','v');
INSERT INTO `CC` VALUES (11,1,9,'19:48:31','r','r');
INSERT INTO `CC` VALUES (12,5,9,'00:00:00','a','a');
INSERT INTO `CC` VALUES (13,3,186,'19:53:05','m','m');
INSERT INTO `CC` VALUES (14,6,NULL,'19:18:56','y','y');
INSERT INTO `CC` VALUES (15,92,2,'10:55:12','j','j');
INSERT INTO `CC` VALUES (16,7,3,'00:25:00','d','d');
INSERT INTO `CC` VALUES (17,NULL,0,'12:35:47','z','z');
INSERT INTO `CC` VALUES (18,3,133,'19:53:03','e','e');
INSERT INTO `CC` VALUES (19,5,1,'17:53:30','h','h');
INSERT INTO `CC` VALUES (20,1,8,'11:35:49','b','b');
INSERT INTO `CC` VALUES (21,2,5,NULL,'s','s');
INSERT INTO `CC` VALUES (22,NULL,5,'06:01:40','e','e');
INSERT INTO `CC` VALUES (23,1,8,'05:45:11','j','j');
INSERT INTO `CC` VALUES (24,0,6,'00:00:00','e','e');
INSERT INTO `CC` VALUES (25,210,51,'00:00:00','f','f');
INSERT INTO `CC` VALUES (26,8,4,'06:11:01','v','v');
INSERT INTO `CC` VALUES (27,7,7,'13:02:46','x','x');
INSERT INTO `CC` VALUES (28,5,6,'21:44:25','m','m');
INSERT INTO `CC` VALUES (29,NULL,4,'22:43:58','c','c');
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_time_key` time 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_time_key` (`col_time_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,'11:28:45','w','w');
INSERT INTO `C` VALUES (2,7,9,'20:25:14','m','m');
INSERT INTO `C` VALUES (3,9,3,'13:47:24','m','m');
INSERT INTO `C` VALUES (4,7,9,'19:24:11','k','k');
INSERT INTO `C` VALUES (5,4,NULL,'15:59:13','r','r');
INSERT INTO `C` VALUES (6,2,9,'00:00:00','t','t');
INSERT INTO `C` VALUES (7,6,3,'15:15:04','j','j');
INSERT INTO `C` VALUES (8,8,8,'11:32:06','u','u');
INSERT INTO `C` VALUES (9,NULL,8,'18:32:33','h','h');
INSERT INTO `C` VALUES (10,5,53,'15:19:25','o','o');
INSERT INTO `C` VALUES (11,NULL,0,'19:03:19',NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'00:39:46','k','k');
INSERT INTO `C` VALUES (13,188,166,NULL,'e','e');
INSERT INTO `C` VALUES (14,2,3,'00:00:00','n','n');
INSERT INTO `C` VALUES (15,1,0,'13:12:11','t','t');
INSERT INTO `C` VALUES (16,1,1,'04:56:48','c','c');
INSERT INTO `C` VALUES (17,0,9,'19:56:05','m','m');
INSERT INTO `C` VALUES (18,9,5,'19:35:19','y','y');
INSERT INTO `C` VALUES (19,NULL,6,'05:03:03','f','f');
INSERT INTO `C` VALUES (20,4,2,'18:38:59','d','d');
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time 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_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'01:13:38','f','f');
SELECT table1 . `pk` AS field1 , (
SELECT MIN( SUBQUERY1_t1 . `col_int_key` ) AS SUBQUERY1_field1
FROM ( B AS SUBQUERY1_t1 INNER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `col_int_key` ) ) ) AS field2 , CONCAT ( table1 . `col_varchar_nokey` , table1 . `col_varchar_nokey` ) AS field3 , table1 . `col_varchar_key` AS field4 , table2 . `col_varchar_nokey` AS field5 , table2 . `col_varchar_key` AS field6 , table1 . `col_time_key` AS field7 , SUM( DISTINCT table1 . `col_int_key` ) AS field8 , table2 . `col_varchar_key` AS field9 , MIN( table2 . `col_int_nokey` ) AS field10 , (
SELECT DISTINCT COUNT( SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field1
FROM ( C AS SUBQUERY2_t1 INNER JOIN ( CC AS SUBQUERY2_t2 LEFT JOIN CC AS SUBQUERY2_t3 ON (SUBQUERY2_t3 . `pk` = SUBQUERY2_t2 . `pk` ) ) ON (SUBQUERY2_t3 . `pk` = SUBQUERY2_t2 . `col_int_key` ) )
WHERE SUBQUERY2_t3 . `col_varchar_key` <> table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_varchar_key` = SUBQUERY2_t1 . `col_varchar_key` ) AS field11 , MAX( table1 . `col_int_nokey` ) AS field12 , MAX( DISTINCT table1 . `col_varchar_nokey` ) AS field13
FROM ( C AS table1 RIGHT JOIN ( ( B AS table2 LEFT JOIN CC AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) )
WHERE ( table3 . `pk` >= (
SELECT COUNT( SUBQUERY3_t1 . `pk` ) AS SUBQUERY3_field1
FROM ( CC AS SUBQUERY3_t1 INNER JOIN CC AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `pk` = SUBQUERY3_t1 . `col_int_nokey` ) ) ) ) OR table1 . `col_int_key` > 77 AND table1 . `col_int_key` < ( 77 + 99 )
GROUP BY field1, field2, field3, field4, field5, field6, field7, field9, field11 ;
|
LPexportBug652763.xml