Comment 1 for bug 612894

Revision history for this message
Philip Stoev (pstoev-askmonty) wrote : Re: Subquery cache returns extra rows on query with view

Test case:

CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) NOT NULL,
  `col_int_key` int(11) NOT NULL,
  `col_date_key` date NOT NULL,
  `col_date_nokey` date NOT NULL,
  `col_time_key` time NOT NULL,
  `col_time_nokey` time NOT NULL,
  `col_datetime_key` datetime NOT NULL,
  `col_datetime_nokey` datetime NOT NULL,
  `col_varchar_key` varchar(1) NOT NULL,
  `col_varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `B` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','p','p');

CREATE VIEW view_B AS SELECT * FROM B;

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) NOT NULL,
  `col_int_key` int(11) NOT NULL,
  `col_varchar_key` varchar(1) NOT NULL,
  `col_varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,3,8,'v','v');
INSERT INTO `CC` VALUES (11,3,8,'f','f');
INSERT INTO `CC` VALUES (12,3,5,'v','v');
INSERT INTO `CC` VALUES (13,2,8,'s','s');
INSERT INTO `CC` VALUES (14,1,8,'a','a');
INSERT INTO `CC` VALUES (15,0,6,'p','p');
INSERT INTO `CC` VALUES (16,8,7,'z','z');
INSERT INTO `CC` VALUES (17,5,2,'a','a');
INSERT INTO `CC` VALUES (18,9,5,'h','h');
INSERT INTO `CC` VALUES (19,5,7,'h','h');
INSERT INTO `CC` VALUES (20,4,2,'v','v');
INSERT INTO `CC` VALUES (21,2,9,'v','v');
INSERT INTO `CC` VALUES (22,33,142,'b','b');
INSERT INTO `CC` VALUES (23,5,3,'y','y');
INSERT INTO `CC` VALUES (24,1,0,'v','v');
INSERT INTO `CC` VALUES (25,9,3,'m','m');
INSERT INTO `CC` VALUES (26,1,5,'z','z');
INSERT INTO `CC` VALUES (27,3,9,'n','n');
INSERT INTO `CC` VALUES (28,8,1,'d','d');
INSERT INTO `CC` VALUES (29,231,107,'a','a');

SET SESSION optimizer_switch = 'subquery_cache=off';

/* cache is ON */ SELECT COUNT(*)
FROM CC table1 STRAIGHT_JOIN ( view_B table2 STRAIGHT_JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` = table2 .`col_varchar_key`
WHERE EXISTS (
SELECT DISTINCT `pk`
FROM CC
WHERE `pk` < table3 .`pk` ) OR table1 .`col_int_nokey` ;

/* cache is OFF */ SELECT COUNT(*)
FROM CC table1 STRAIGHT_JOIN ( view_B table2 STRAIGHT_JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` = table2 .`col_varchar_key`
WHERE EXISTS (
SELECT DISTINCT `pk`
FROM CC
WHERE `pk` < table3 .`pk` ) OR table1 .`col_int_nokey` ;