Bug #79110 When casting a BIG number to unsigned, get inconsistent result for same number
Submitted: 4 Nov 2015 4:17 Modified: 4 Nov 2015 7:22
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8,5.1.77,5.5.47,5.6.27,5.7.9 OS:Any
Assigned to: CPU Architecture:Any

[4 Nov 2015 4:17] Su Dylan
Description:
Output:
=======
mysql> select cast('-184467440737095516150' as unsigned), cast(-184467440737095516150 as unsigned), version();
+--------------------------------------------+------------------------------------------+-----------+
| cast('-184467440737095516150' as unsigned) | cast(-184467440737095516150 as unsigned) | version() |
+--------------------------------------------+------------------------------------------+-----------+
|                        9223372036854775808 |                                        0 | 5.7.8-rc  |
+--------------------------------------------+------------------------------------------+-----------+
1 row in set, 2 warnings (0.00 sec)

Problem:
========
A consistent result is expected for the two expression, no matter with the BIG number in string format or numeric format.

How to repeat:
select cast('-184467440737095516150' as unsigned), cast(-184467440737095516150 as unsigned), version();

Suggested fix:
The two expressions in this sql should return consistent result.
[4 Nov 2015 6:31] Peter Laursen
Further observation: 

SELECT 
  CAST(
    '-184467440737095516150' AS UNSIGNED -- returns 9223372036854775808
  ),
  CAST(
    '-12233720368547758080' AS UNSIGNED -- returns 9223372036854775808
  ),
  CAST(
    '-9233720368547758080' AS UNSIGNED -- returns 9223372036854775808
  ),
  CAST(
    '-5233720368547758080' AS UNSIGNED -- returns 13213023705161793536
  );

So a large interval of argument negative numbers (at least from  -184467440737095516150 to -9233720368547758080) all return same value (9223372036854775808) when argument to CAST() is passed as a 'string'.

(This is on MySQL 5.6, BTW).
[4 Nov 2015 7:22] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.
Observed this in 5.1.77/5.5.47/5.6.27/5.7.9.

Thanks,
Umesh