Bug #46551 '= ANY' subqueries are not processed properly - returning 0 rows
Submitted: 4 Aug 2009 17:29 Modified: 1 Oct 2009 8:37
Reporter: Patrick Crews Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: ANY, materialization, optimizer_switch, semijoin, subquery
Triage: Triaged: D2 (Serious)

[4 Aug 2009 17:29] Patrick Crews
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.
[14 Sep 2009 12:52] Roy Lyseng
Running with

set @@optimizer_switch='default,materialization=off'

I get correct results.
[29 Sep 2009 11:36] Øystein Grøvlen
Query plans are the same as for WL#46548 both for with and without materialization.

One can fix the problem by turning off either materialization or semijoin.
[30 Sep 2009 10:24] Øystein Grøvlen
The change to setup_sj_materialization shown below, fixes the problem.
That is, disabling caching of key values.  
Next step is to look into why the caching does not work ...

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc	2009-09-29 10:45:07 +0000
+++ sql/sql_select.cc	2009-09-30 10:14:45 +0000
@@ -9791,7 +9791,7 @@ bool setup_sj_materialization(JOIN_TAB *
     tab_ref->key_buff2=tab_ref->key_buff+ALIGN_SIZE(tmp_key->key_length);
     tab_ref->key_err=1;
     tab_ref->null_rejecting= 1;
-    tab_ref->disable_cache= FALSE;
+    tab_ref->disable_cache= TRUE;
 
     KEY_PART_INFO *cur_key_part= tmp_key->key_part;
     store_key **ref_key= tab_ref->key_copy;
[1 Oct 2009 8:37] Øystein Grøvlen
This is a duplicate of Bug#46548.