Bug #46517 Queries with constant / null only WHERE clause interpreted differently in azalea
Submitted: 2 Aug 2009 21:54
Reporter: Patrick Crews Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4,5.6.99 OS:Any
Assigned to: CPU Architecture:Any
Tags: azalea, constant, is_null
Triage: Triaged: D2 (Serious)

[2 Aug 2009 21:54] Patrick Crews
Description:
Queries with constant / IS NULL only WHERE clauses are being interpreted differently in azalea and 5.1 (differing result sets).

This does not appear to be affected by join_cache_level and most other optimizer switches were off (see exact settings in attached test case):

SELECT `varchar_nokey`  
FROM C  
WHERE `int_key`  =  134  OR 'y' OR  249  AND `int_key`  IS  NULL  AND `varchar_nokey`   

returns a single row, while 5.1 returns none:
# Diff:

# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95812-1249144548-server0.dump	2009-08-01 12:35:48.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95812-1249144548-server1.dump	2009-08-01 12:35:48.000000000 -0400
# @@ -0,0 +1 @@
# +z

How to repeat:
Test case:

# 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=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off';

# 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 = 'disable';

# The value of engine_condition_pushdown is distinct between the two servers:
# Server 0 : SET SESSION engine_condition_pushdown = 'ON';
# Server 1 : SET SESSION engine_condition_pushdown = 0;

# 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 C;
--enable_warnings

CREATE TABLE `C` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,'g'),(1,'v'),(247,'t'),(7,'u'),(5,'n'),(4,'p'),(1,NULL),(5,'u'),(4,'n'),(9,NULL),(1,'k'),(166,'e'),(3,'d'),(6,'t'),(5,'o'),(9,'e'),(5,'s'),(6,NULL),(4,'d'),(NULL,'z');

d
 
SELECT `varchar_nokey`  
FROM C  
WHERE `int_key`  =  134  OR 'y' OR  249  AND `int_key`  IS  NULL  AND `varchar_nokey`   ;

# Diff:

# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95812-1249144548-server0.dump	2009-08-01 12:35:48.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95812-1249144548-server1.dump	2009-08-01 12:35:48.000000000 -0400
# @@ -0,0 +1 @@
# +z

DROP TABLE C;

# End of test case for query 0

# Begin test case for query 1

--disable_warnings
DROP TABLE IF EXISTS C;
--enable_warnings

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  `time_key` time DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,1,'18:52:56','g','g'),(2,1,'03:03:27','v','v'),(3,247,'07:48:21','t','t'),(4,7,'04:13:17','u','u'),(5,5,'11:58:42','n','n'),(6,4,'22:13:15','p','p'),(7,1,'13:05:44',NULL,NULL),(8,5,'21:46:44','u','u'),(9,4,'15:25:57','n','n'),(10,9,'19:13:57',NULL,NULL),(11,1,'00:39:30','k','k'),(12,166,NULL,'e','e'),(13,3,'00:41:08','d','d'),(14,6,'13:04:05','t','t'),(15,5,'04:20:48','o','o'),(16,9,'21:56:05','e','e'),(17,5,'19:35:19','s','s'),(18,6,'00:00:00',NULL,NULL),(19,4,'22:38:59','d','d'),(20,NULL,NULL,'z','z');

 
SELECT  MAX( `time_key`) AS field1 , `varchar_nokey` AS field2 
FROM C AS table1 
WHERE ( table1 . `int_key` = 134 OR ( ( table1 . `varchar_key` != 'y' OR table1 . `int_key` > 249 AND table1 . `int_key` < ( 249 + 197 ) ) AND table1 . `int_key` IS  NULL AND table1 .`pk` <= table1 . `varchar_nokey` ) AND table1 . `varchar_key` < table1 . `varchar_nokey` ) ;

# Diff:

# --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95812-1249144549-server0.dump	2009-08-01 12:35:49.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen95812-1249144549-server1.dump	2009-08-01 12:35:49.000000000 -0400
# @@ -1 +1 @@
# -NULL	NULL
# +NULL	z

DROP TABLE C;

# End of test case for query 1

Suggested fix:
Ensure consistent, correct server behavior.
[14 Oct 2010 6:41] Roy Lyseng
Adding version number 5.6 (5.4 is obsolete).

Apparently, this error does no longer occur, but need to analyze more in-depth before we can close as Can't repeat.