CODEWITHSUNDEEP
mysql
jurchiks
jurchiks

Reputation: 1433

MySQL how to make negative results possible when subtracting unsigned values?

I have three tables joined by left join. Here's the code: 

SELECT
(LEAST(`a`.`price, `b`.`price`) - `c`.`price`) AS `diff`
...
ORDER BY `diff` DESC

The problem: c.price is greater than the LEAST, thus the subtraction is negative and throws BIGINT UNSIGNED value is out of range. How can I make it NOT throw this ridiculous error?
This is result data, I'm not modifying the actual data in the table, so why does it not allow me to do this normally? I've tried CAST(LEAST(...) AS SIGNED) and casting both columns inside LEAST as signed, neither worked.

Upvotes: 7

Views: 4810

Answers (2)

Alberto León
Alberto León

Reputation: 2921

Cast as SIGNED each number before LEAST and before substract

SELECT
(LEAST(CAST(`a`.`price` AS SIGNED), CAST(`b`.`price` AS SIGNED)) - CAST(`c`.`price` AS SIGNED)) AS `diff`
...
ORDER BY `diff` DESC

Upvotes: 8

kor_
kor_

Reputation: 1530

You may want to check the NO_UNSIGNED_SUBTRACTION operator: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_no_unsigned_subtraction.

There are risks in using it, though: http://datacharmer.blogspot.fi/2006/11/hidden-risks-of-sql-mode.html

Upvotes: 1

Related Questions