Description:
Azalea is not processing '= ANY' subqueries properly - returning 0 rows instead of correct result sets.
From the attached test case:
SELECT `pk` FROM D WHERE `varchar_nokey` = ANY ( SELECT `varchar_key` FROM B WHERE `pk` >= 'b' ) ;
Should return: (as it does in 5.1)
pk
71
79
87
91
However, azalea returns 0 rows for the same query and data. Initial testing does not show this to be affected by optimizer_switch, optimizer_use_mrr, engine_condition_pushdown, or join_cache_level settings.
This seems related to Bug#46548: Azalea not processing subquery WHERE clauses properly w/ materialization = on as IN and = ANY are supposed to be synonyms, however this issue is not affected by the materialization switch.
How to repeat:
Test case.
Uncomment the appropriate Server0 / Server1 lines depending on which version of the server is being used.
# 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, B;
--enable_warnings
CREATE TABLE `D` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` 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','o'),(2,'o','o'),(3,'o','o'),(4,'d','d'),(5,'v','v'),(6,'e','e'),(7,'h','h'),(8,'l','l'),(9,'m','m'),(10,'f','f'),(11,NULL,NULL),(12,'a','a'),(13,'u','u'),(14,'r','r'),(15,NULL,NULL),(16,'l','l'),(17,'q','q'),(18,'r','r'),(19,NULL,NULL),(20,NULL,NULL),(21,'j','j'),(22,'o','o'),(23,'f','f'),(24,'s','s'),(25,'a','a'),(26,'w','w'),(27,'c','c'),(28,'b','b'),(29,'c','c'),(30,'d','d'),(31,'r','r'),(32,'w','w'),(33,'r','r'),(34,'q','q'),(35,'g','g'),(36,NULL,NULL),(37,'c','c'),(38,'h','h'),(39,'n','n'),(40,'i','i'),(41,'r','r'),(42,'e','e'),(43,'b','b'),(44,'s','s'),(45,'a','a'),(46,'k','k'),(47,'k','k'),(48,'v','v'),(49,'d','d'),(50,'g','g'),(51,'l','l'),(52,'p','p'),(53,'j','j'),(54,'k','k'),(55,'n','n'),(56,'v','v'),(57,'p','p'),(58,'t','t'),(59,'r','r'),(60,'t','t'),(61,NULL,NULL),(62,'y','y'),(63,'w','w'),(64,'t','t'),(65,NULL,NULL),(66,'r','r'),(67,'e','e'),(68,'q','q'),(69,'m','m'),(70,'z','z'),(71,'x','x'),(72,'c','c'),(73,'g','g'),(74,'c','c'),(75,'k','k'),(76,'q','q'),(77,'h','h'),(78,'u','u'),(79,'x','x'),(80,'r','r'),(81,'e','e'),(82,'g','g'),(83,'a','a'),(84,'z','z'),(85,'z','z'),(86,'m','m'),(87,'x','x'),(88,'g','g'),(89,'p','p'),(90,'c','c'),(91,'x','x'),(92,'g','g'),(93,'g','g'),(94,'c','c'),(95,'q','q'),(96,'j','j'),(97,'s','s'),(98,'n','n'),(99,'f','f'),(100,'o','o');
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,NULL,NULL),(2,'x','x');
SELECT `pk`
FROM D
WHERE `varchar_nokey` = ANY (
SELECT `varchar_key`
FROM B
WHERE `pk` >= 'b' ) ;
# Diff:
# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399727-server0.dump 2009-08-04 11:28:47.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399727-server1.dump 2009-08-04 11:28:47.000000000 -0400
# @@ -1,4 +0,0 @@
# -71
# -79
# -87
# -91
DROP TABLE D, B;
# End of test case for query 0
# Begin test case for query 1
--disable_warnings
DROP TABLE IF EXISTS D, B;
--enable_warnings
CREATE TABLE `D` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` 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,8,'o','o'),(2,0,'o','o'),(3,6,'o','o'),(4,3,'d','d'),(5,9,'v','v'),(6,4,'e','e'),(7,1,'h','h'),(8,8,'l','l'),(9,4,'m','m'),(10,7,'f','f'),(11,8,NULL,NULL),(12,NULL,'a','a'),(13,0,'u','u'),(14,1,'r','r'),(15,1,NULL,NULL),(16,4,'l','l'),(17,251,'q','q'),(18,0,'r','r'),(19,6,NULL,NULL),(20,1,NULL,NULL),(21,6,'j','j'),(22,2,'o','o'),(23,NULL,'f','f'),(24,5,'s','s'),(25,0,'a','a'),(26,7,'w','w'),(27,6,'c','c'),(28,1,'b','b'),(29,4,'c','c'),(30,4,'d','d'),(31,8,'r','r'),(32,149,'w','w'),(33,7,'r','r'),(34,3,'q','q'),(35,8,'g','g'),(36,1,NULL,NULL),(37,5,'c','c'),(38,2,'h','h'),(39,2,'n','n'),(40,7,'i','i'),(41,4,'r','r'),(42,7,'e','e'),(43,7,'b','b'),(44,1,'s','s'),(45,6,'a','a'),(46,4,'k','k'),(47,8,'k','k'),(48,3,'v','v'),(49,4,'d','d'),(50,5,'g','g'),(51,NULL,'l','l'),(52,3,'p','p'),(53,9,'j','j'),(54,1,'k','k'),(55,0,'n','n'),(56,6,'v','v'),(57,5,'p','p'),(58,7,'t','t'),(59,3,'r','r'),(60,109,'t','t'),(61,8,NULL,NULL),(62,5,'y','y'),(63,1,'w','w'),(64,1,'t','t'),(65,28,NULL,NULL),(66,1,'r','r'),(67,NULL,'e','e'),(68,5,'q','q'),(69,0,'m','m'),(70,2,'z','z'),(71,6,'x','x'),(72,0,'c','c'),(73,4,'g','g'),(74,9,'c','c'),(75,8,'k','k'),(76,NULL,'q','q'),(77,248,'h','h'),(78,5,'u','u'),(79,2,'x','x'),(80,7,'r','r'),(81,5,'e','e'),(82,NULL,'g','g'),(83,6,'a','a'),(84,5,'z','z'),(85,5,'z','z'),(86,1,'m','m'),(87,1,'x','x'),(88,0,'g','g'),(89,7,'p','p'),(90,1,'c','c'),(91,6,'x','x'),(92,6,'g','g'),(93,5,'g','g'),(94,8,'c','c'),(95,NULL,'q','q'),(96,3,'j','j'),(97,0,'s','s'),(98,NULL,'n','n'),(99,61,'f','f'),(100,1,'o','o');
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,NULL,NULL,NULL),(2,9,'x','x');
SELECT `int_nokey` AS field1 , `varchar_key` AS field2 , `pk` AS field3
FROM D AS table1
WHERE ( table1 . `varchar_nokey` = ANY (
SELECT subquery_t1 . `varchar_key`
FROM B AS subquery_t1
WHERE subquery_t1 . `pk` >= 'b' )) ;
# Diff:
# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399728-server0.dump 2009-08-04 11:28:48.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen69966-1249399728-server1.dump 2009-08-04 11:28:48.000000000 -0400
# @@ -1,4 +0,0 @@
# -1 x 87
# -2 x 79
# -6 x 71
# -6 x 91
DROP TABLE D, B;
# End of test case for query 1
Suggested fix:
Ensure correct processing of queries.