Bug #81963 | Get incorrect result for -2 into unsigned column and cast -2 to unsigned | ||
---|---|---|---|
Submitted: | 22 Jun 2016 2:37 | Modified: | 1 Jul 2016 10:22 |
Reporter: | Su Dylan | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.7.8, 5.6.31, 5.7.13 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[22 Jun 2016 2:37]
Su Dylan
[24 Jun 2016 9:15]
MySQL Verification Team
Hello Su Dylan, Thank you for the report and test case. Thanks, Umesh
[24 Jun 2016 9:16]
MySQL Verification Team
Inconsistencies in the message as well mysql> show warnings; +---------+------+-------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------+ | Warning | 1105 | Cast to unsigned converted negative integer to it's positive complement | +---------+------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> \q bin/perror 1105 MySQL error code 1105 (ER_UNKNOWN_ERROR): Unknown error
[1 Jul 2016 10:22]
Erlend Dahl
Posted by developer: [27 Jun 2016 3:37] Roy Lyseng This is not a bug. It is quite obvious that the statement "insert into t1(c1) values(-2);" should cause an error when c1 is an unsigned integer, since -2 is outside the valid range of c1 (at least in strict mode). However, the CAST operation explicitly allows conversion of negative signed values into unsigned ones: "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 13.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 Thus, it is clear that an explicit CAST can be used to interpret a signed bit pattern as an unsigned one.
[24 Oct 2019 10:46]
Steve Shaw
If the goal is to put the result into an unsigned integer (4 byte) column, then mask by the maximum valid unsigned value. Otherwise, it will overflow, and ALL your negative values will be truncated to the maximum unsigned integer: update mytable set myuint = cast(myint as unsigned integer) & 0xFFFFFFFF;