Bug #43865 Submitted: CAST( -1.0 as UNSIGNED) yields 0 instead of MAXINT and returns a warning. 25 Mar 2009 20:04 26 Mar 2009 6:48 Justin Swanhart Verified None MySQL Server: General S2 (Serious) 5.1.30, 5.0, 5.1, 6.0 bzr Any Any Triaged: D2 (Serious)

[25 Mar 2009 20:04] Justin Swanhart
```Description:
create table t1(c1 int signed,
c2 decimal(5,2),
c3 float(5,2)
);

insert into t1 values (-1,-1,-1);

ACTUAL RESULT:

select cast(-1.0 as unsigned), cast(c1 as unsigned), cast(c2 as unsigned), cast(c3 as unsigned) from t1 \G
*************************** 1. row ***************************
cast(-1.0 as unsigned): 0
cast(c1 as unsigned): 18446744073709551615
cast(c2 as unsigned): 0
cast(c3 as unsigned): 18446744073709551615
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '' |
| Error | 1292 | Truncated incorrect DECIMAL value: '' |
+-------+------+---------------------------------------+
2 rows in set (0.01 sec)

EXPECTED RESULT:
cast(-1.0 as unsigned): 18446744073709551615
cast(c1 as unsigned): 18446744073709551615
cast(c2 as unsigned): 18446744073709551615
cast(c3 as unsigned): 18446744073709551615
1 row in set, 0 warnings (0.01 sec)

How to repeat:
create table t1(c1 int signed,
c2 decimal(5,2),
c3 float(5,2)
);

insert into t1 values (-1,-1,-1);
select cast(-1.0 as unsigned), cast(c1 as unsigned), cast(c2 as unsigned), cast(c3 as unsigned) from t1 \G

Suggested fix:
-1 when cast to unsigned should yield MAXINT, regardless of the underlying data type.  It works as expected for FLOAT, but not for DECIMAL.```
[26 Mar 2009 6:48] Sveta Smirnova
```Thank you for the report.

Verified as described.

Workaround:

cast(cast(DECIMAL_VALUE as signed) as unsigned);```
[16 Aug 2013 19:56] Justin Swanhart
`Still a problem in 5.6.13`