Bug #102911 BETWEEN SQL-query on auto-incremented primary key field gives the incorrect resu
Submitted: 10 Mar 23:49 Modified: 11 Mar 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 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 5:52] MySQL Verification Team
Hello Sergey,

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

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