MySQL casting of -1 returns 18446744073709551615

Question

I can't seem to find an explanation for this and I'm pretty sure that it has previously worked as expected.

SELECT CAST(-1 AS UNSIGNED INTEGER);

Expected: 0
Result: 18446744073709551615

Has something changed, or is this a MySQL bug?

[UPDATE] OK, I think that I found a reason why it had appeared to work before:

SELECT CAST(-1.0 AS UNSIGNED INTEGER);
+--------------------------------+
| CAST(-1.0 AS UNSIGNED INTEGER) |
+--------------------------------+
|                              0 |
+--------------------------------+

Now, can someone please explain the difference that this causes?.. Actually, I found in the docs!

If either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule.

Answer 1

From the docs:

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned by default (see Section 11.6.1, “Arithmetic Operators”). You can override this by using the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT CAST(1-2 AS UNSIGNED)
       -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
       -> -1

Basically, with you cast you tell MySQL how to treat 0xFFFFFFFFFFFFFFFF. It's -1 when signed, 18446744073709551615 when unsigned.