Bug #72046 UNSIGNED BIGINT high values wrong numerical comparison results

• Submitted: 15 Mar 2014 10:35
• Modified: 1 Apr 2014 7:50
• Reporter: Mohamed Nabil
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.6, 5.5.38, 5.1.73, 5.6.18
• OS: Any
• CPU Architecture: Any
• Tags: BIGINT UNSIGNED

[15 Mar 2014 10:35] Mohamed Nabil

Description:
When using UNSIGNED BIGINT field in WHERE clause numerical comparisons, fields contains high value return wrong comparison results.

Instead of showing complex example, this direct example explains the problem of fields contains exact values.

SELECT (18446744073709551615 BETWEEN 0 AND 18446744073709551615); => returns 0 (false)
SELECT (184467440737095 BETWEEN 0 AND 18446744073709551500); => returns 0 (false)

while:
SELECT (13 BETWEEN 0 AND 13); => returns 1 (true)
SELECT (184467440737095 BETWEEN 0 AND 1844674407370955150); => returns 1 (true)

How to repeat:
-

Suggested fix:
-

[16 Mar 2014 6:44] MySQL Verification Team

Hello Nabil,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[19 Mar 2014 13:49] Jørgen Løland

Duplicate of BUG#70622

[31 Mar 2014 16:32] Paul DuBois

Noted in 5.7.5 changelog.

Some comparisons between BIGINT signed and unsigned values could
yield incorrect results.

[1 Apr 2014 7:50] Mohamed Nabil

@Paul Dubois

for the current issue, Instead of using BETWEEN:
SELECT (17000000000000000000 BETWEEN 0 AND 18446744073709551615);

Use Equivalent <= AND <= :
SELECT (0 <= 17000000000000000000 AND 17000000000000000000 <= 18446744073709551615);