MySQL Bugs: #79295: select cast(-1 as unsigned) returns 18446744073709551615

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

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.

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

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.

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.