Bug #83902 Incorrect 'ERROR 1690 (22003): BIGINT UNSIGNED value is out of range' error

• Submitted: 21 Nov 2016 8:49
• Modified: 4 Jan 2020 10:17
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.5.53, 5.6.34, 5.7.16
• OS: Any
• CPU Architecture: Any

[21 Nov 2016 8:49] Roel Van de Paar

Description:
Consider;

mysql> select ((floor(-(401))) * 4108050209);
+--------------------------------+
| ((floor(-(401))) * 4108050209) |
+--------------------------------+
|                 -1647328133809 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CRC32(0);
+------------+
| CRC32(0)   |
+------------+
| 4108050209 |
+------------+
1 row in set (0.00 sec)

But;

mysql> select ((floor(-(401))) * CRC32(0));
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(floor(-(401)) * crc32(0))'

How to repeat:
select ((floor(-(401))) * CRC32(0));

[21 Nov 2016 9:11] MySQL Verification Team

Hello Roel,

Thank you for the report.

Thanks,
Umesh

[4 Jan 2020 10:17] Roy Lyseng

Posted by developer:
 
This is not a bug.
The CRC32 function produces an UNSIGNED value, and the UNSIGNED property is propagated to the addition operator.
Workaround is to convert the CRC32 result to a signed integer:

  select ((floor(-(401))) * CAST(CRC32(0) AS SIGNED));