[MDEV-25112] MIN/MAX aggregation over an indexed column may return wrong result Created: 2021-03-10  Updated: 2021-03-19  Resolved: 2021-03-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.5.9, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: Sergey Lebedev Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: None


 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

 Comments   
Comment by Alice Sherepa [ 2021-03-11 ]

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 |
 
+---------+

Comment by Igor Babaev [ 2021-03-11 ]

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 |
 
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

Comment by Igor Babaev [ 2021-03-11 ]

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)

Comment by Valerii Kravchuk [ 2021-03-11 ]

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)

Comment by Sergey Lebedev [ 2021-03-11 ]

Yes, the bug also appears in MySQL and we've reported it.

Comment by Igor Babaev [ 2021-03-12 ]

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)

Comment by Dmitry Shulga [ 2021-03-18 ]

Approved

Comment by Igor Babaev [ 2021-03-19 ]

A fix for this bug was pushed into 10.2

Generated at Thu Feb 08 09:35:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.