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

[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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.061 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.