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:
None 
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
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).
[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;