Search code examples
mysqlsqlintegerunsigned

Cannot subtract one value from another (unsigned)

I have a table stats with three columns:

  • id
  • up - number of upvotes (just like here at StackOverflow)
  • down - analogue to up

up and down are INT(11) and UNSIGNED (because they'll only be positive values).

Now when I want to fetch the ten items with the highest (up-down) value, I'm using this query:

SELECT id, up, down, (up-down) AS result
FROM stats
ORDER BY result DESC
LIMIT 0,10

But I'm getting an error

#1690 - BIGINT UNSIGNED value is out of range in
        '(`database`.`stats`.`up` - `database`.`stats`.`down`)'

If I leave out the ORDER BY result DESC everything runs smoothly (except for the fact that they're not ordered by result, but the math of up-down is working).

What would I have to change in my query in order to retreive the correct result? Or do I have to remove the UNSIGNED attribute? But isn't this an appropriate case where I should use that attribute?

Solution

  • Unsigned remain unsigned, so you have a problem when the result would be negative. Cast to signed before the subtraction:

    SELECT id, up, down, cast(up as signed) - cast(down as signed) AS result
FROM stats
ORDER BY result DESC
LIMIT 0, 10;

    Or, keep your query and add a where clause:

    SELECT id, up, down, (up-down) AS result
FROM stats
WHERE up >= down
ORDER BY result DESC
LIMIT 0,10;