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 */
[12 Jan 2010 17:12]
Patrick Crews
Closing as duplicate of Bug#36300, have updated that bug and have moved it from Need Feedback back to verified.
Will also reference this bug on that report.
[15 Jan 2010 0:49]
Patrick Crews
As noted in Bug#36300, it is possible to work around this bug by rewriting the query as col < 2 OR col > 7. I made a mistake in my query rewrite (used AND)
Page generated in 0.041 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.