Bug #102911 BETWEEN SQL-query on auto-incremented primary key field gives the incorrect resu

Submitted: 10 Mar 2021 23:49 Modified: 11 Mar 2021 5:52
Reporter: Sergey Lebedev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7, 8.0, 5.7.33, 8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[10 Mar 2021 23:49] Sergey Lebedev
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 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)

gives the correct result:
max(id)
287

How to repeat:
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 http://sqlfiddle.com/#!9/ab9f6d/2
[11 Mar 2021 5:52] MySQL Verification Team
Hello Sergey,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[12 Mar 2021 6:09] MySQL Verification Team
Bug #102935 marked as duplicate of this one

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

Page generated in 0.053 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.