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
Triage: Triaged: D2 (Serious)

[23 Apr 2008 17:49] Jonathan Svensson-Köhler
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
[23 Apr 2008 18:06] Peter Laursen
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)
[23 Apr 2008 18:38] Jonathan Svensson-Köhler
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 ;
[23 Apr 2008 19:05] Valeriy Kravchuk
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.
[23 May 2008 23:00] Bugs System
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".
[12 Jan 2010 17:11] Patrick Crews
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
[12 Jan 2010 17:11] Patrick Crews
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
Closed Bug#50250	MIN / MAX failing on query with simple WHERE condition with BETWEEN as a duplicate of this one.
[12 Jan 2010 17:26] Valeriy Kravchuk
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.