MySQL Bugs: #61925: Wrong result with aggregate + NOT BETWEEN + key

Bug #61925	Wrong result with aggregate + NOT BETWEEN + key
Submitted:	20 Jul 2011 10:10	Modified:	28 Jan 2012 14:59
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0/5.1/5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Repeatable in mysql-5.1,5.5. The following query:

SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;

returns NULL even though there are obviously rows that match the WHERE predicate.

explain:

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:
CREATE TABLE t1 (a int, KEY (a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use d3
Database changed
mysql 5.5 >drop table if exists t1;
Query OK, 0 rows affected (0.04 sec)

mysql 5.5 >CREATE TABLE t1 (a int, KEY (a));
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 >INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql 5.5 >SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;
+--------+
| MAX(a) |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql 5.5 >alter table t1 drop index a;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;
+--------+
| MAX(a) |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

mysql 5.5 >

Thank you for the bug report. Verified as described on 5.0 too.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.6. Documented in the 5.6.5 changelog as follows:

      A query that used an aggregate function of an index with BETWEEN 
      in the WHERE clause could fail to match some or any rows, thus 
      returning an invalid result.

Closed.

s/BETWEEN/NOT BETWEEN/ in previous comment. :)