| 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 | |
[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;
Description: Output: === mysql> create table t1(c1 bigint unsigned); rt into t1 values(-2); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(-2); ERROR 1264 (22003): Out of range value for column 'c1' at row 1 mysql> show warnings; +-------+------+---------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------+ | Error | 1264 | Out of range value for column 'c1' at row 1 | +-------+------+---------------------------------------------+ 1 row in set (0.00 sec) mysql> select cast(-2 as unsigned), cast('-2' as unsigned); +----------------------+------------------------+ | cast(-2 as unsigned) | cast('-2' as unsigned) | +----------------------+------------------------+ | 18446744073709551614 | 18446744073709551614 | +----------------------+------------------------+ 1 row in set, 1 warning (0.00 sec) 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> select version(); +--------------+ | version() | +--------------+ | 5.7.8-rc-log | +--------------+ 1 row in set (0.00 sec) Problem: === When inserting -2 into unsigned column and casting -2 to unsigned, different result is returned. How to repeat: drop table t1; create table t1(c1 bigint unsigned); insert into t1 values(-2); show warnings; select cast(-2 as unsigned), cast('-2' as unsigned); show warnings; Suggested fix: Consistent result is expected. 0 is expected for all the 3 situations: 1. insert negative number into unsigned int; 2. cast(-2 as unsigned); 3. cast('-2' as unsigned).