Web lists-archives.com

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