Monthly Archives: October 2014

BIGINT UNSIGNED value is out of range

Ran into a strange issue with a MySQL query.

Here is a table I got:

The main point to notice is that there are two UNSIGNED columns: num_up_votes and num_down_votes.

I tried to run this query:

and MySQL gave me this error:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '('0' - '18')'

Could not understand why MySQL threw error for this. Of course, the result is negative, but I am not trying to store anything into an UNSIGNED column.

A google search led me to the MySQL manual’s out of range and overflow page.

where it states

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default.

This is a really strange behavior!

For now, there are two solutions:

  1. Use CAST with SIGNED as follows in the SQL itself:

  2. If you are using an interactive client, you can set sql_mode like this:

    which then allows subtraction between unsigned column values when the difference is negative.