Bug #99135 BETWEEN malfunctions when comparing large numbers

• Submitted: 31 Mar 2020 16:54
• Modified: 1 Apr 2020 5:07
• Reporter: Manuel Rigger
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.19, 5.6.47, 5.7.29
• OS: Any
• CPU Architecture: Any

[31 Mar 2020 16:54] Manuel Rigger

Description:
Consider the following statements:

How to repeat:
CREATE TABLE t0(c0 INT UNIQUE);
INSERT INTO t0(c0) VALUES(0);
SELECT * FROM t0 WHERE t0.c0 BETWEEN 18446744073561222871 AND 0; -- expected: {0}, actual: {}?

That no row is fetched contracts with the following statement evaluating to TRUE:

SELECT t0.c0 BETWEEN 18446744073561222871 AND 0 FROM t0; -- 1

It might also be that the second SELECT computes an incorrect result. In each case, one of the two statements should fetch a row:

SELECT * FROM t0 WHERE t0.c0 BETWEEN 18446744073561222871 AND 0;
SELECT * FROM t0 WHERE t0.c0 NOT BETWEEN 18446744073561222871 AND 0;

When removing the UNIQUE constraint, the first query fetches a row.

I found this bug based on MySQL 8.0.19

[1 Apr 2020 5:07] MySQL Verification Team

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

[1 Apr 2020 5:10] MySQL Verification Team

- Observed reported behavior in 5.6.47, 5.7.29 and 8.0.19 builds