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

[12 Jul 2013 2:18] Arthur O'Dwyer
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.
[12 Jul 2013 17:33] MySQL Verification Team
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) >
[12 Jul 2018 12:54] Hartmut Holzgraefe
Still present in 5.5.60, not showing in later release series

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.