MySQL Bugs: #69729: Bad interaction between MIN/MAX and "NOT BETWEEN" leads to wrong results

Bug #69729	Bad interaction between MIN/MAX and "NOT BETWEEN" leads to wrong results
Submitted:	12 Jul 2013 2:18	Modified:	12 Jul 2013 17:33
Reporter:	Arthur O'Dwyer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.5.31, 5.5.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
$ mysqld --version
mysqld  Ver 5.5.31-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu))

Applying MIN or MAX over a column filtered by "col NOT BETWEEN x AND y" causes MySQL to generate the same range scan bounds as for "col BETWEEN x AND y"; but then of course the filter rejects every row in the scan.

The similar aggregates AVG, SUM, and COUNT do not have this bug; only MIN and MAX do.

How to repeat:
DROP DATABASE IF EXISTS d;
CREATE DATABASE d;
USE d;
CREATE TABLE t (id INT);
INSERT INTO t VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;

  +---------+
  | MIN(id) |
  +---------+
  |       1 |
  +---------+

-- So far so good. Now index the table and try again...

ALTER TABLE t ADD KEY (id);
SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;

  +---------+
  | MIN(id) |
  +---------+
  |    NULL |
  +---------+

-- Oh no! MySQL started its table scan at id=4 and stopped it at id=8,
-- so no rows matched!

Suggested fix:
I assume the bug is somewhere inside get_best_group_min_max(), but that's all I've got.

Hello Arthur,

Thank you for the bug report. 
Verified as described on recent 5.5.31/32.

Thanks,
Umesh

//

// 5.5.32 - affected

node1 [localhost] {msandbox} ((none)) > select version();
+------------+
| version()  |
+------------+
| 5.5.32-log |
+------------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} ((none)) > DROP DATABASE IF EXISTS d;
Query OK, 0 rows affected, 1 warning (0.00 sec)

node1 [localhost] {msandbox} ((none)) > CREATE DATABASE d;
Query OK, 1 row affected (0.00 sec)

node1 [localhost] {msandbox} ((none)) > USE d;
Database changed
node1 [localhost] {msandbox} (d) > CREATE TABLE t (id INT);
Query OK, 0 rows affected (0.11 sec)

node1 [localhost] {msandbox} (d) > INSERT INTO t VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

node1 [localhost] {msandbox} (d) > SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+---------+
| MIN(id) |
+---------+
|       1 |
+---------+
1 row in set (0.02 sec)

node1 [localhost] {msandbox} (d) > explain SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} (d) > ALTER TABLE t ADD KEY (id);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost] {msandbox} (d) > SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+---------+
| MIN(id) |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} (d) > explain SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
| 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)

// 5.6.12 - Not affected

node1 [localhost] {msandbox} ((none)) > select version();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} ((none)) > DROP DATABASE IF EXISTS d;
Query OK, 0 rows affected, 1 warning (0.00 sec)

node1 [localhost] {msandbox} ((none)) > CREATE DATABASE d;
Query OK, 1 row affected (0.00 sec)

node1 [localhost] {msandbox} ((none)) > USE d;
Database changed
node1 [localhost] {msandbox} (d) > CREATE TABLE t (id INT);
Query OK, 0 rows affected (0.38 sec)

node1 [localhost] {msandbox} (d) > INSERT INTO t VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
Query OK, 10 rows affected (0.17 sec)
Records: 10  Duplicates: 0  Warnings: 0

node1 [localhost] {msandbox} (d) > SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+---------+
| MIN(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} (d) > explain SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} (d) > ALTER TABLE t ADD KEY (id);
Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost] {msandbox} (d) > SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+---------+
| MIN(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} (d) > explain SELECT MIN(id) FROM t WHERE id NOT BETWEEN 4 AND 8;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | id            | id   | 5       | NULL |    4 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} (d) >

Still present in 5.5.60, not showing in later release series