Bug #79878 "-1 BETWEEN 9223372036854775808 AND 1" gives True
Submitted: 8 Jan 2016 6:58 Modified: 7 Oct 2020 16:24
Reporter: Su Dylan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.8, 5.6.28, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[8 Jan 2016 6:58] Su Dylan
Description:
Output:
=====
mysql> select ( -1 BETWEEN 9223372036854775808 AND 1 );
+------------------------------------------+
| ( -1 BETWEEN 9223372036854775808 AND 1 ) |
+------------------------------------------+
|                                        1 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=====
0 expected.

How to repeat:
select ( -1 BETWEEN 9223372036854775808 AND 1 );

Suggested fix:
select ( -1 BETWEEN 9223372036854775808 AND 1 ) returns 0.
[8 Jan 2016 8:06] MySQL Verification Team
Thank you for the report.
Observed this with 5.7.10 and 5.6.28 builds.

Thanks,
Umesh
[7 Oct 2020 16:24] Paul DuBois
Posted by developer:
 
Fixed in 8.0.23.

Some corner cases for val1 BETWEEEN val2 AND val3 were fixed, such as
that -1 BETWEEN 9223372036854775808 AND 1 returned true.