Bug #79295 select cast(-1 as unsigned) returns 18446744073709551615

Submitted: 16 Nov 2015 15:48 Modified: 28 Aug 2017 14:57
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[16 Nov 2015 15:48] Su Dylan
Description:
Output:
=======
mysql> select cast(-1 as unsigned);
+----------------------+
| cast(-1 as unsigned) |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

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

Problem:
========
What is the logic to handle the situation of converting a negative number to unsigned int?
If there is any documentation related to this, please point it to me, Thanks.

How to repeat:
select cast(-1 as unsigned);

Suggested fix:
What is the logic to handle the situation of converting a negative number to unsigned int?
If there is any documentation related to this, please point it to me, Thanks.
[17 Nov 2015 5:14] Peter Laursen
SELECT CAST(-1 AS UNSIGNED); -- 18446744073709551615
SELECT CAST(-2 AS UNSIGNED); -- 18446744073709551614
SELECT CAST(-3 AS UNSIGNED); -- 18446744073709551613
-- etc.

http://dev.mysql.com/doc/refman/5.7/en/integer-types.html tells that 18446744073709551615 is highest BIGINT UNSIGNED (ie. the biggest integer value at all), that MySQL can handle. 

So "-1" simply 'wraps around' the BIGINT UNSIGNED range of 0 .. 18446744073709551615 to "18446744073709551615" with this CAST().

I have seen the same thing happening in other contexts with MySQL. And what else should happen? An error like "argument of of range" or similar should be returned? I got used to it, actually. 

Whether this is a bug or not is a matter of taste, I think. But I think at least it is expected behavior in this (MySQL) part of the world. :-)

-- Peter
-- not an Oracle/MySQL person
[17 Nov 2015 5:30] Peter Laursen
This actually surprises me:

SELECT CAST(18446744073709551616 AS UNSIGNED); -- 18446744073709551615
SELECT CAST(18446744073709551617 AS UNSIGNED); -- 18446744073709551615
-- etc.

I would have expected a "wrap-over" happening here too and result to be "0" and "1". But it does not happen, as I expected.

This is an inconsistent behavior of how CAST_TO_UNSIGNED treats positive and negative numbers. It would be consistent with the behavior above to always return "0" for "SELECT CAST(negative_number as UNSIGNED)". So I have now made up my mind and agree with you that your test case is a bug!

I do remember that many years back I observed some 'oddities' with negative numbers in MySQL.  However such cases have no practical importance to me so I probably did not follow-up on this.
[28 Aug 2017 14:57] MySQL Verification Team
Hi!

SQL standard SQL2011, states quite precisely, in its chapter 4.4.2, that exception condition is to be raised in the cases described above.

Hence, this is a verified bug.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.