Re: Something strange here...

2017/06/13 17:42 ... Chris Knipe:
> Can someone explain to me why this is happening please:
> 
> mysql> SELECT * FROM CustomerDetails WHERE Username=’blah’\G
> *************************** 1. row ***************************
>           EntryID: F83D4109-DCA8-426F-98B1-00B4AF117ACB
>          Username: blah
>     AccountVolume: 0
> 1 row in set (0.00 sec)
> 
> mysql> UPDATE CustomerDetails SET AccountVolume=GREATEST(CAST(AccountVolume-2865 AS SIGNED), CAST(0 AS SIGNED)) WHERE Username='blah';
> ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`CustomerDetails`.`AccountVolume` - 2865)'
> 
> Using GREATEST, shouldn’t it be irrelevant whether AccountVolume-INT is signed or unsigned?

The expression you need is something like this,
... = IF(AccountVolume < 2865, 0, AccountVolume-2865)...
, because the complaint arises from the subtraction which turns 
negative, which, for UNSIGNED integers, is out of range. The GREATEST is 
apply'd after the subtraction.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql