Description:
The server is failing to provide proper MIN / MAX results for this query:
SELECT MIN( `pk` )
FROM W
WHERE `pk` NOT BETWEEN 2 AND 7 ;
This is present in all versions of the server from 5.0 on.
Removing the NOT will produce correct results.
Converting the WHERE condition into pk < 2 AND pk > 7 still produces bad results.
Converting the WHERE condition to pk >=2 and pk <= 7 will produce proper results.
It appears that the server cannot calculate what is NOT BETWEEN two values here, no matter how we phrase the condition.
This appears to be the same bug noted here:
Bug#36300 - min() and max() functions returns NULL when using between in where
^ It should be noted that the user's description includes a NOT BETWEEN rather than just BETWEEN
EXPLAIN (original query)
EXPLAIN SELECT MIN(pk), MAX( `pk` )
FROM W
WHERE `pk` NOT BETWEEN 2 AND 7 ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching min/max row
EXPLAIN (BETWEEN query - rather than NOT BETWEEN)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
How to repeat:
MTR test case.
/* Server0: MySQL 6.0.14-alpha-debug-log */
/* Server1: JavaDB Version N/A */
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ W;
--enable_warnings
CREATE TABLE `W` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`pk`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;
INSERT INTO `W` VALUES (1);
INSERT INTO `W` VALUES (2);
INSERT INTO `W` VALUES (3);
INSERT INTO `W` VALUES (4);
INSERT INTO `W` VALUES (5);
INSERT INTO `W` VALUES (6);
INSERT INTO `W` VALUES (7);
INSERT INTO `W` VALUES (8);
INSERT INTO `W` VALUES (9);
INSERT INTO `W` VALUES (10);
INSERT INTO `W` VALUES (11);
INSERT INTO `W` VALUES (12);
INSERT INTO `W` VALUES (13);
INSERT INTO `W` VALUES (14);
INSERT INTO `W` VALUES (15);
INSERT INTO `W` VALUES (16);
INSERT INTO `W` VALUES (17);
INSERT INTO `W` VALUES (18);
INSERT INTO `W` VALUES (19);
INSERT INTO `W` VALUES (20);
INSERT INTO `W` VALUES (21);
INSERT INTO `W` VALUES (22);
SELECT MIN( `pk` )
FROM W
WHERE `pk` NOT BETWEEN 2 AND 7 ;
/* Diff: */
/* --- /tmp//randgen24411-1263165740-server0.dump 2010-01-10 18:22:20.000000000 -0500
# +++ /tmp//randgen24411-1263165740-server1.dump 2010-01-10 18:22:20.000000000 -0500
# @@ -1 +1 @@
# -NULL
# +1 */
DROP TABLE W;
#/* End of test case for query 0 */