Fwd: Re: Something strange here...

2017-06-26 Thread shawn l.green 

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 
Organization: Oracle Corporation
To: Chris Knipe 

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

Re: Something strange here...

2017-06-26 Thread Hal.sz S.ndor 

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

Something strange here...

2017-06-13 Thread Chris Knipe 
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.

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