MySQL: Casting confusion or bug?

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

Am I confused, has something changed, or is this a MySQL bug?

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.