ikromm ikromm - 4 months ago 34
MySQL Question

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


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.

Answer

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 unsigned when using it in a mixed-sign context, something like this maybe:

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