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.