Description:
Azalea seems to be returning truncated result sets for certain WHERE / IN subqueries as compared to 5.1:
SELECT `pk`
FROM D
WHERE `varchar_key` IN (
SELECT `varchar_key` ) ;
Returns all `pk` values from table D in 5.1 (100 rows returned).
While the same query only returns the following in azalea:
1
2
3
22
100
Initial testing did not indicate that this was affected by optimizer_switch, engine_condition_pushdown, optimizer_use_mrr or join_cache_level variable settings.
How to repeat:
RQG-generated test file:
Run against both 5.1 and azalea, uncomment the appropriate Server0 / Server1 lines in the test depending on server version. I left the diffs (commented out) in-place for reference.
# Server0: version = 5.1.38-debug-log
# Server1: version = 5.4.4-alpha-debug-log
# The value of optimizer_switch is distinct between the two servers:
# Server 0 : SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on';
# Server 1 : 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';
# The value of optimizer_use_mrr is distinct between the two servers:
# Server 0 : SET SESSION optimizer_use_mrr = '';
# Server 1 : SET SESSION optimizer_use_mrr = 'force';
# The value of engine_condition_pushdown is common between the two servers:
SET SESSION engine_condition_pushdown = ON ;
# The value of join_cache_level is distinct between the two servers:
# Server 0 : SET SESSION join_cache_level = '';
# Server 1 : SET SESSION join_cache_level = 1;
# Begin test case for query 0
--disable_warnings
DROP TABLE IF EXISTS D;
--enable_warnings
CREATE TABLE `D` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,'o'),(2,'o'),(3,'o'),(4,'d'),(5,'v'),(6,'e'),(7,'h'),(8,'l'),(9,'m'),(10,'f'),(11,NULL),(12,'a'),(13,'u'),(14,'r'),(15,NULL),(16,'l'),(17,'q'),(18,'r'),(19,NULL),(20,NULL),(21,'j'),(22,'o'),(23,'f'),(24,'s'),(25,'a'),(26,'w'),(27,'c'),(28,'b'),(29,'c'),(30,'d'),(31,'r'),(32,'w'),(33,'r'),(34,'q'),(35,'g'),(36,NULL),(37,'c'),(38,'h'),(39,'n'),(40,'i'),(41,'r'),(42,'e'),(43,'b'),(44,'s'),(45,'a'),(46,'k'),(47,'k'),(48,'v'),(49,'d'),(50,'g'),(51,'l'),(52,'p'),(53,'j'),(54,'k'),(55,'n'),(56,'v'),(57,'p'),(58,'t'),(59,'r'),(60,'t'),(61,NULL),(62,'y'),(63,'w'),(64,'t'),(65,NULL),(66,'r'),(67,'e'),(68,'q'),(69,'m'),(70,'z'),(71,'x'),(72,'c'),(73,'g'),(74,'c'),(75,'k'),(76,'q'),(77,'h'),(78,'u'),(79,'x'),(80,'r'),(81,'e'),(82,'g'),(83,'a'),(84,'z'),(85,'z'),(86,'m'),(87,'x'),(88,'g'),(89,'p'),(90,'c'),(91,'x'),(92,'g'),(93,'g'),(94,'c'),(95,'q'),(96,'j'),(97,'s'),(98,'n'),(99,'f'),(100,'o');
SELECT `pk`
FROM D
WHERE `varchar_key` IN (
SELECT `varchar_key` ) ;
# Diff:
# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399445-server0.dump 2009-08-04 11:24:05.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399445-server1.dump 2009-08-04 11:24:05.000000000 -0400
# @@ -1,93 +1,5 @@
# 1
# -10
# 100
# -12
# -13
# -14
# -16
# -17
# -18
# 2
# -21
# 22
# -23
# -24
# -25
# -26
# -27
# -28
# -29
# 3
# -30
# -31
# -32
# -33
# -34
# -35
# -37
# -38
# -39
# -4
# -40
# -41
# -42
# -43
# -44
# -45
# -46
# -47
# -48
# -49
# -5
# -50
# -51
# -52
# -53
# -54
# -55
# -56
# -57
# -58
# -59
# -6
# -60
# -62
# -63
# -64
# -66
# -67
# -68
# -69
# -7
# -70
# -71
# -72
# -73
# -74
# -75
# -76
# -77
# -78
# -79
# -8
# -80
# -81
# -82
# -83
# -84
# -85
# -86
# -87
# -88
# -89
# -9
# -90
# -91
# -92
# -93
# -94
# -95
# -96
# -97
# -98
# -99
DROP TABLE D;
# End of test case for query 0
# Begin test case for query 1
--disable_warnings
DROP TABLE IF EXISTS C, D, B;
--enable_warnings
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`int_key` int(11) DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,1,'g'),(2,0,1,'v'),(3,199,247,'t'),(4,3,7,'u'),(5,7,5,'n'),(6,3,4,'p'),(7,8,1,NULL),(8,9,5,'u'),(9,9,4,'n'),(10,0,9,NULL),(11,0,1,'k'),(12,188,166,'e'),(13,2,3,'d'),(14,5,6,'t'),(15,5,5,'o'),(16,6,9,'e'),(17,7,5,'s'),(18,2,6,NULL),(19,2,4,'d'),(20,6,NULL,'z');
CREATE TABLE `D` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`int_key` int(11) DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,8,0,'o'),(2,0,6,'o'),(3,6,7,'o'),(4,3,8,'d'),(5,9,4,'v'),(6,4,6,'e'),(7,1,1,'h'),(8,8,NULL,'l'),(9,4,6,'m'),(10,7,7,'f'),(11,8,8,NULL),(12,NULL,8,'a'),(13,0,1,'u'),(14,1,1,'r'),(15,1,5,NULL),(16,4,4,'l'),(17,251,39,'q'),(18,0,2,'r'),(19,6,NULL,NULL),(20,1,2,NULL),(21,6,3,'j'),(22,2,5,'o'),(23,NULL,4,'f'),(24,5,NULL,'s'),(25,0,4,'a'),(26,7,2,'w'),(27,6,4,'c'),(28,1,1,'b'),(29,4,1,'c'),(30,4,6,'d'),(31,8,8,'r'),(32,149,12,'w'),(33,7,7,'r'),(34,3,4,'q'),(35,8,6,'g'),(36,1,3,NULL),(37,5,8,'c'),(38,2,6,'h'),(39,2,3,'n'),(40,7,4,'i'),(41,4,6,'r'),(42,7,3,'e'),(43,7,2,'b'),(44,1,6,'s'),(45,6,1,'a'),(46,4,5,'k'),(47,8,8,'k'),(48,3,8,'v'),(49,4,6,'d'),(50,5,5,'g'),(51,NULL,3,'l'),(52,3,9,'p'),(53,9,0,'j'),(54,1,NULL,'k'),(55,0,NULL,'n'),(56,6,2,'v'),(57,5,2,'p'),(58,7,NULL,'t'),(59,3,NULL,'r'),(60,109,77,'t'),(61,8,4,NULL),(62,5,0,'y'),(63,1,NULL,'w'),(64,1,8,'t'),(65,28,195,NULL),(66,1,0,'r'),(67,NULL,2,'e'),(68,5,3,'q'),(69,0,0,'m'),(70,2,2,'z'),(71,6,1,'x'),(72,0,5,'c'),(73,4,1,'g'),(74,9,2,'c'),(75,8,0,'k'),(76,NULL,2,'q'),(77,248,25,'h'),(78,5,9,'u'),(79,2,2,'x'),(80,7,NULL,'r'),(81,5,4,'e'),(82,NULL,2,'g'),(83,6,2,'a'),(84,5,138,'z'),(85,5,6,'z'),(86,1,5,'m'),(87,1,9,'x'),(88,0,5,'g'),(89,7,7,'p'),(90,1,1,'c'),(91,6,5,'x'),(92,6,1,'g'),(93,5,9,'g'),(94,8,2,'c'),(95,NULL,4,'q'),(96,3,6,'j'),(97,0,0,'s'),(98,NULL,145,'n'),(99,61,13,'f'),(100,1,1,'o');
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`int_key` int(11) DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,NULL,7,NULL),(2,9,4,'x');
SELECT MIN( `varchar_key` ) AS field1, MAX( `varchar_key` ) AS field2, `int_key` AS field3 , `int_key` AS field4 , `varchar_key` AS field5 , `pk` AS field6
FROM D AS table1
WHERE ( ( table1 . `varchar_key` IN (
SELECT subquery_t1 . `varchar_key`
FROM D AS subquery_t1
WHERE subquery_t1 . `int_nokey` >= 21 )) AND ( EXISTS ( (
SELECT subquery_t1 . `pk`
FROM C AS subquery_t1 RIGHT JOIN B AS subquery_t2 ON ( subquery_t2 . `int_nokey` = subquery_t1 . `pk` )
WHERE subquery_t1 . `int_key` < 8 ) ) ) )
GROUP BY field3, field4, field5, field6;
# Diff:
# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399448-server0.dump 2009-08-04 11:24:08.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399448-server1.dump 2009-08-04 11:24:08.000000000 -0400
# @@ -1,17 +0,0 @@
# -f f 13 13 f 99
# -f f 4 4 f 23
# -f f 7 7 f 10
# -h h 1 1 h 7
# -h h 25 25 h 77
# -h h 6 6 h 38
# -q q 2 2 q 76
# -q q 3 3 q 68
# -q q 39 39 q 17
# -q q 4 4 q 34
# -q q 4 4 q 95
# -t t 77 77 t 60
# -t t 8 8 t 64
# -t t NULL NULL t 58
# -w w 12 12 w 32
# -w w 2 2 w 26
# -w w NULL NULL w 63
DROP TABLE C, D, B;
# End of test case for query 1
Suggested fix:
Ensure correct processing of queries.
