Comment 1 for bug 601156

Revision history for this message
Philip Stoev (pstoev-askmonty) wrote : Re: item_subselect.cc:4635: virtual void subselect_partial_match_engine::print(String*, enum_query_type): Assertion `(0)' failed.

Automatic test case:

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

CREATE TABLE `CC` (
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `CC` VALUES (7,8,'v');
INSERT INTO `CC` VALUES (1,9,'r');
INSERT INTO `CC` VALUES (5,9,'a');
INSERT INTO `CC` VALUES (3,186,'m');
INSERT INTO `CC` VALUES (6,NULL,'y');
INSERT INTO `CC` VALUES (92,2,'j');
INSERT INTO `CC` VALUES (7,3,'d');
INSERT INTO `CC` VALUES (NULL,0,'z');
INSERT INTO `CC` VALUES (3,133,'e');
INSERT INTO `CC` VALUES (5,1,'h');
INSERT INTO `CC` VALUES (1,8,'b');
INSERT INTO `CC` VALUES (2,5,'s');
INSERT INTO `CC` VALUES (NULL,5,'e');
INSERT INTO `CC` VALUES (1,8,'j');
INSERT INTO `CC` VALUES (0,6,'e');
INSERT INTO `CC` VALUES (210,51,'f');
INSERT INTO `CC` VALUES (8,4,'v');
INSERT INTO `CC` VALUES (7,7,'x');
INSERT INTO `CC` VALUES (5,6,'m');
INSERT INTO `CC` VALUES (NULL,4,'c');
CREATE TABLE `C` (
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `C` VALUES (NULL,2,'w');
INSERT INTO `C` VALUES (7,9,'m');
INSERT INTO `C` VALUES (9,3,'m');
INSERT INTO `C` VALUES (7,9,'k');
INSERT INTO `C` VALUES (4,NULL,'r');
INSERT INTO `C` VALUES (2,9,'t');
INSERT INTO `C` VALUES (6,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (NULL,8,'h');
INSERT INTO `C` VALUES (5,53,'o');
INSERT INTO `C` VALUES (NULL,0,NULL);
INSERT INTO `C` VALUES (6,5,'k');
INSERT INTO `C` VALUES (188,166,'e');
INSERT INTO `C` VALUES (2,3,'n');
INSERT INTO `C` VALUES (1,0,'t');
INSERT INTO `C` VALUES (1,1,'c');
INSERT INTO `C` VALUES (0,9,'m');
INSERT INTO `C` VALUES (9,5,'y');
INSERT INTO `C` VALUES (NULL,6,'f');
INSERT INTO `C` VALUES (4,2,'d');

 EXPLAIN EXTENDED
SELECT `col_int_key`
FROM (
SELECT SUBQUERY1_t1 .*
FROM CC SUBQUERY1_t1 STRAIGHT_JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_varchar_key` AND SUBQUERY1_t1 .`col_int_nokey` NOT IN (
SELECT `col_int_key`
FROM C ) ) table1 ;

DROP TABLE CC;
DROP TABLE C;