Bug #96006 BETWEEN operator computes incorrect results for large numbers

• Submitted: 26 Jun 2019 15:39
• Modified: 27 Jun 2019 2:17
• Reporter: Manuel Rigger
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6/5.7/8.0
• OS: Ubuntu
• CPU Architecture: x86

[26 Jun 2019 15:39] Manuel Rigger

Description:
The BETWEEN operator computes incorrect results for large numbers.

How to repeat:
The example below checks if 0 is between CAST(-1 AS UNSIGNED) and 0:

SELECT 0 BETWEEN 18446744073709551615 AND 0; -- expected: FALSE, actual: TRUE

Strangely, the operator seems to work with large numbers given as an upper bound:

SELECT 0 BETWEEN 0 AND 18446744073709551615; -- TRUE

This results in a surprising behavior for statements such as the following:

SELECT 0 BETWEEN 10000000000000000000 AND 18000000000000000000; -- expected: FALSE, unexpected: TRUE

The documentation states that the first statement should be equivalent to the following, which works as expected:

SELECT 18446744073709551615 <= 0 AND 0 <= 0; -- FALSE

Could it be that the first comparison is performed as a signed 64-bit compare, and the second one as an unsigned compare?

[27 Jun 2019 2:17] MySQL Verification Team

Thank you for the bug report.

[27 Jun 2019 7:24] Tor Didriksen

Posted by developer:
 
In 5.7 and 8.0

SELECT cast(0 as unsigned) BETWEEN 18446744073709551615 AND 0;
cast(0 as unsigned) BETWEEN 18446744073709551615 AND 0
0

SELECT cast(0 as unsigned) BETWEEN 10000000000000000000 AND 18000000000000000000;
cast(0 as unsigned) BETWEEN 10000000000000000000 AND 18000000000000000000
0