Fwd: Re: Something strange here...

Hello List,

So sorry about the bad click. I meant to "Reply to list" but instead 
just replied to the original poster.

This is the exact same advice that hsv@  just provided. If I had paid 
attention I could have saved him the duplication of efforts. My 
apologies to him and everyone else.

Humbly embarrassed,
Shawn


-------- Forwarded Message --------
Subject: Re: Something strange here...
Date: Wed, 14 Jun 2017 14:04:02 -0400
From: shawn l.green <[email protected]>
Organization: Oracle Corporation
To: Chris Knipe <[email protected]>

Hello Chris,

On 6/13/2017 5:42 PM, Chris Knipe wrote:
> Hi all,
>
> 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?
>
> How would I go about doing this?  I have played quite a bit with CAST here, but I am not having much luck.
>
> Thnx,
> Chris.
>

If I read between the lines, I think you created AccountVolume as a
"BIGINT UNSIGNED" column. Right?

When you subtract something from a 0 BIGINT UNSIGNED column, you are
attempting to make a negative BIGINT UNSIGNED value (which is illegal)

Have you tried casting the column to SIGNED before the subtraction.
Instead of this...
CAST(AccountVolume-2865 AS SIGNED)

Try this
(CAST(AccountVolume AS SIGNED) - 2865)


That should get through the first part of the problem. But you still
need to re-cast the result of the GREATEST function back to an UNSIGNED
value so that it matches the type of the left side of the assignment
operator


(showing any earlier attempts to fix the problem when engaging outside
resources for help can save guessing time)


Another way to avoid this problem is to use something like an IF()
function to avoid going out of range

AccountVolume = IF(AccountVolume > 2865, AccountVolume-2865, 0)



-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.



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