Details
Description
For a DB Table having a id field which is the table’s auto-incremented primary key
SQL-query:
SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id BETWEEN 267 AND 287)
gives the result:
max(id)
267
, which is incorrect as id BETWEEN 267 AND 287 should be equivalent to (id >= 267 AND id <= 287 and the max id value that satisfies the condition is 287, not 267
At the same time SQL-query
SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id >= 267 AND id <= 287)
max(id)
gives the correct result:
max(id)
287
here is a minimal example to create DB table and reproduce the issue
CREATE TABLE _between_bug2 (id int(13) auto_increment primary key);
INSERT INTO _between_bug2 (id) VALUES (267);
INSERT INTO _between_bug2 (id) VALUES (287);
INSERT INTO _between_bug2 (id) VALUES (303);
SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id BETWEEN 267 AND 287)
and here is an SQL fiddle to reproduce it
Attachments
Activity
The problem can be reproduced with any index. Here' s a test case demonstrating this.
create table t1 (a int, index idx(a)) engine=myisam; |
insert into t1 values (267), (273), (287), (303), (308); |
select max(a) from t1 where a < 303 and (a between 267 AND 287); |
MariaDB [test]> select max(a) from t1 where a < 303 and (a between 267 AND 287);
|
+--------+
|
| max(a) |
|
+--------+
|
| 273 |
|
+--------+
|
We see that MIN/MAX optimization is applied here:
MariaDB [test]> explain select max(a) from t1 where a < 303 and (a between 267 AND 287);
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 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 |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
Interesting that the following query
select max(a) from t1 where a <= 303 and (a between 267 AND 287); |
works fine and with the same optimization applied
|
MariaDB [test]> select max(a) from t1 where a <= 303 and (a between 267 AND 287);
|
+--------+
|
| max(a) |
|
+--------+
|
| 287 |
|
+--------+
|
|
MariaDB [test]> explain select max(a) from t1 where a <= 303 and (a between 267 AND 287);
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 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 |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
MySQL-8.0.21 has the same bug:
mysql> select version();
|
+--------------+
|
| version() |
|
+--------------+
|
| 8.0.21-debug |
|
+--------------+
|
1 row in set (0.00 sec)
|
|
mysql> create table t1 (a int, index idx(a)) engine=myisam;
|
Query OK, 0 rows affected (0.01 sec)
|
|
mysql> insert into t1 values (267), (273), (287), (303), (308);
|
Query OK, 5 rows affected (0.00 sec)
|
Records: 5 Duplicates: 0 Warnings: 0
|
|
mysql> select max(a) from t1 where a < 303 and (a between 267 AND 287);
|
+--------+
|
| max(a) |
|
+--------+
|
| 273 |
|
+--------+
|
1 row in set (0.00 sec)
|
|
mysql> explain select max(a) from t1 where a < 303 and (a between 267 AND 287);
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
mysql> select max(a) from t1 where a <= 303 and (a between 267 AND 287);
|
+--------+
|
| max(a) |
|
+--------+
|
| 287 |
|
+--------+
|
1 row in set (0.01 sec)
|
|
mysql> explain select max(a) from t1 where a <= 303 and (a between 267 AND 287);
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Same with 8.0.23:
openxs@ao756:~/dbs/8.0$ bin/mysql -uroot test --socket=/tmp/mysql8.sock
|
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 10
|
Server version: 8.0.23 Source distribution
|
|
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
|
|
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> drop table if exists t1;
|
Query OK, 0 rows affected (1.57 sec)
|
|
mysql> create table t1 (a int, index idx(a)) engine=myisam;
|
Query OK, 0 rows affected (0.15 sec)
|
|
mysql>
|
mysql> insert into t1 values (267), (273), (287), (303), (308);
|
Query OK, 5 rows affected (0.09 sec)
|
Records: 5 Duplicates: 0 Warnings: 0
|
|
mysql>
|
mysql> select max(a) from t1 where a < 303 and (a between 267 AND 287);
|
+--------+
|
| max(a) |
|
+--------+
|
| 273 |
|
+--------+
|
1 row in set (0.02 sec)
|
|
mysql> select max(a) from t1 where a < 303 and (a >= 267 AND a <= 287);
|
+--------+
|
| max(a) |
|
+--------+
|
| 287 |
|
+--------+
|
1 row in set (0.00 sec)
|
|
mysql> explain select max(a) from t1 where a < 303 and (a between 267 AND 287);
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
A similar problem can be observed here:
MariaDB [test]> select min(a) from t1 where a > 267 and (a between 273 AND 303);
|
+--------+
|
| min(a) |
|
+--------+
|
| 287 |
|
+--------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select min(a) from t1 where a >= 267 and (a between 273 AND 303);
|
+--------+
|
| min(a) |
|
+--------+
|
| 273 |
|
+--------+
|
1 row in set (0.00 sec)
|
Thanks! Reproducible on 5.5-10.5 as described
MariaDB [test]> SELECT max(id) FROM _between_bug2 WHERE id < 288 and (id BETWEEN 267 AND 287);
+---------+
| max(id) |
+---------+
| 267 |
+---------+
MariaDB [test]> SELECT max(id) FROM _between_bug2 WHERE id <= 287 and (id BETWEEN 267 AND 287);
+---------+
| max(id) |
+---------+
| 287 |
+---------+