MySQL Bugs: #36300: min() and max() functions returns NULL when using NOT + BETWEEN in where

Bug #36300	min() and max() functions returns NULL when using NOT + BETWEEN in where
Submitted:	23 Apr 2008 17:49	Modified:	12 Jan 2010 17:26
Reporter:	Jonathan Svensson-Köhler	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.26, 5.0.32, 5.0+, 5.1.43-bzr, 6.0.14-bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Aggregate Function, NOT BETWEEN

Description:
I´ve got a problem with a sql query. I´m trying to get the min and max values of two fields when a field is between two values. So I wrote a query like this:
    SELECT min( lft ) , max( rgt ) FROM `groups` WHERE lft NOT BETWEEN 1 AND 1233;

And it returned

min(lft) 	max(rgt)
NULL 		NULL

... then to check if I wrote wrong i tryed:
    SELECT * FROM `groups` WHERE lft NOT BETWEEN 1 AND 1233;

It returned 322 rows.
WIERD!

I tryed a lot of combinations but the only thing working was:
    SELECT min( lft )  ,max( rgt ) FROM `groups` WHERE NOT (lft >=1 AND lft <= 1233)

min(lft) 	max(rgt)
1234 		2562

EXPLAIN SELECT min( lft ) , max( rgt ) FROM `groups` WHERE lft NOT BETWEEN 1 AND 1233;
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

How to repeat:
DESCRIBE groups; 
gid 	int(11) 	NO 	PRI 	NULL 	auto_increment
parent 	int(11) 	NO 	  	  	 
name 	varchar(255) 	NO 	MUL 	  	 
lft 	int(11) 	NO 	MUL 	  	 
rgt 	int(11) 	NO

I found:
http://bugs.mysql.com/bug.php?id=16377
Maybe you should try a more recent server?

Also it would make life easier for everyone if your test case was *importable* (SQL dump/script format)

peter
(not a mysql person)

I´ve read about that one but the problem is not that BETWEEN returns wrong but MIN() and MAX() returns NULL. BETWEEN works just fine when using * as select fields

CREATE TABLE `groups` (
  `gid` int(11) NOT NULL auto_increment,
  `parent` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  PRIMARY KEY  (`gid`),
  KEY `lft` (`lft`,`rgt`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16535 ;

Please, try to repeat with a newer version, 5.0.51a. In case of the same problem, please, send complete test case, with all the data for the table.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This is only occurring on a NOT BETWEEN.  
MIN / MAX work as expected on the same range, but using BETWEEN.
This also occurs if you rephrase the NOT BETWEEN using <,>
  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. (same as 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

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 */

Closed Bug#50250	MIN / MAX failing on query with simple WHERE condition with BETWEEN as a duplicate of this one.

This is a very old bug in optimizer it seems:

77-52-7-73:4.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.26-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `W` (
    ->   `pk` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`pk`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (1);
Query OK, 1 row affected (0,03 sec)

mysql> INSERT INTO `W` VALUES (2);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (3);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (4);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (5);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (6);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (7);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (8);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (9);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (10);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (11);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (12);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (13);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (14);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (15);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (16);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (17);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (18);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (19);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (20);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (21);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `W` VALUES (22);
Query OK, 1 row affected (0,00 sec)

mysql> 
mysql>  
mysql> SELECT  MIN( `pk`  )  
    -> FROM W  
    -> WHERE `pk`  NOT  BETWEEN  2  AND  7   ;
+--------------+
| MIN( `pk`  ) |
+--------------+
|         NULL |
+--------------+
1 row in set (0,00 sec)

mysql> SELECT  `pk`   FROM W   WHERE `pk`  NOT  BETWEEN  2  AND  7;
+----+
| pk |
+----+
|  1 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
+----+
16 rows in set (0,00 sec)

mysql> explain SELECT  `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      | W     | index | PRIMARY       | PRIMARY |       4 | NULL |   22 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0,00 sec)

mysql> explain SELECT  MIN(`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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
1 row in set (0,00 sec)

mysql> SELECT  MIN( `pk`  )   FROM W   WHERE `pk`  <  2  OR `pk` > 7;
+--------------+
| MIN( `pk`  ) |
+--------------+
|            1 |
+--------------+
1 row in set (0,00 sec)

Workaround is to rewrite the query as shown above.