ikromm ikromm - 1 year ago 195
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 Source

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

From the data type docs:


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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download