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

[20 Jul 2011 10:10] Philip Stoev
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;
[20 Jul 2011 10:23] Miguel Solorzano
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 >
[20 Jul 2011 10:33] Miguel Solorzano
Thank you for the bug report. Verified as described on 5.0 too.
[28 Jan 2012 14:59] Jon Stephens
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
[28 Jan 2012 15:01] Jon Stephens
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.
[28 Jan 2012 15:02] Jon Stephens
s/BETWEEN/NOT BETWEEN/ in previous comment. :)