| Bug #43865 | CAST( -1.0 as UNSIGNED) yields 0 instead of MAXINT and returns a warning. | ||
|---|---|---|---|
| Submitted: | 25 Mar 2009 20:04 | Modified: | 4 Aug 2020 23:47 |
| Reporter: | Justin Swanhart | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.1.30, 5.0, 5.1, 6.0 bzr | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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
[4 Aug 2020 23:47]
Justin Swanhart
Fixed in 8.0.21 not sure when it was fixed or if it still affects other versions.
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.