Bug #50250 MIN / MAX failing on query with simple WHERE condition with BETWEEN
Submitted: 11 Jan 2010 18:18 Modified: 12 Jan 2010 17:12
Reporter: Patrick Crews Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: CPU Architecture:Any
Tags: Aggregate Function, between, not

[11 Jan 2010 18:18] Patrick Crews
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)