MySQL BIGINT UNSINGED value is out or range while multiplying with (-1)

Question

Possible Duplicate:
BIGINT Out-of-range Error since MySQL 5.5

Anyone has any ideas why the following produces a 1690 error: BIGINT UNSIGNED value is out or range?

SELECT CAST(IF(trades.`buyer` = 63, -1, 1)  * trades.`price` * trades.`amount` AS SIGNED) AS priceTotal 
FROM trades
WHERE (trades.`buyer` = 63 OR trades.`seller`= 63);

What I'm trying to do is make the priceTotal negative when the user is the buyer.

Error Code: 1690 BIGINT UNSIGNED value is out of range in '(if((`trades`.`buyer` = 63),-(1),1) * `trades`.`price`)' — ikromm, Nov 12 '11 at 13:53
Even though [this doc page](http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtraction) is talking about subtraction, I imagine the same principle applies. _By default, subtraction between integer operands produces an UNSIGNED result if any operand is UNSIGNED._ — Wiseguy, Nov 12 '11 at 14:22
Also, same conclusion found in [this question](http://stackoverflow.com/questions/5743069/bigint-out-of-range-error-since-mysql-5-5). — Wiseguy, Nov 12 '11 at 14:25

score 7 · Accepted Answer · edited Oct 28 '16 at 03:16

7

Unsigned values have to be zero or more - so -1 is out of range. Don't use unsigned maybe?

From the data type docs:

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

Or cast each value to signed when using it in a mixed-sign context, something like this maybe:

IF(trades.`buyer` = 63, -1, 1) * CAST(trades.`price` AS SIGNED) * CAST(trades.`amount` AS SIGNED)

edited Oct 28 '16 at 03:16

Nisse Engström

4,738
23
27
42

answered Nov 12 '11 at 13:52

martin clayton

76,436
32
213
198

you mean the `price` and `amount` cols need to be signed? – ikromm Nov 12 '11 at 13:54
@JohnKrommidas - if you want to store a negative value in there they would need to be signed, yes. – martin clayton Nov 12 '11 at 13:56
1

I just want it to be displayed negative in the result set. I'm not storing any negative values in my database. – ikromm Nov 12 '11 at 13:57
The `price` and `amount` columns are of type UNSIGNED SMALLINT(5) – ikromm Nov 12 '11 at 13:58
There is a typo (SGINED) in the code. Can't fix it myself since the edit is < 6 characters. – Lokal_Profil Aug 04 '16 at 14:26

MySQL BIGINT UNSINGED value is out or range while multiplying with (-1)

1 Answers1