BIGINT UNSIGNED VALUE IS out of range My SQL

Question

I'm getting the following error

#1690 - BIGINT UNSIGNED value is out of range in '(legends.spawns.quantity - tmp_field)'

Here is my query

SELECT drops.common, drops.uncommon, drops.rare, drops.legendary, spawns . *
     , ( quantity - COUNT( game_moblist.spawn_id ) ) AS quantity_to_spawn
     , mobs . * 
FROM spawns
     LEFT JOIN mobs
          USING ( mob_id ) 
     LEFT JOIN game_moblist
          USING ( spawn_id ) 
     LEFT JOIN drops ON ( 
               SELECT MAX( level ) 
                 FROM drops
                WHERE drops.type = mobs.drop_list
                  AND drops.level <= spawns.level ) 
GROUP BY spawn_id
HAVING quantity_to_spawn >=0
       AND next_spawn <=0

I've been staring at it a while the query is long I'm sorry.

spawns table - count game_moblist.spawn_id is 0 for all possible rows but 1 (I deleted a row to test the query)

The data otherwise is quite long and irrelevant to my question I think

Any idea how to get around this error?

Answer 1

Please read "Out-of-Range and Overflow Handling".
It says:

As of MySQL 5.5.5, overflow during numeric expression evaluation results in an error. For example, the largest signed BIGINT value is 9223372036854775807, so the following expression produces an error.

mysql> SELECT 9223372036854775807 + 1;

ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

To enable the operation to succeed in this case, convert the value to unsigned;

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
|                       9223372036854775808 |
+-------------------------------------------+

A change to part of your query, as following, would solve the issue.

( CAST( quantity AS SIGNED ) - COUNT( game_moblist.spawn_id ) ) AS quantity_to_spawn

Otherwise you may require to change the sql_mode on unsigned operations.

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

and then run your query to get desired output.

See also a similar posting answered on a forum here.

Answer 2

To generalise the rule, MySQL will now refuse to substract an UNSIGNED operand from a SIGNED one.

Example : SELECT A - B; will fail if A is SIGNED whereas B is UNSIGNED.

Workarounds: Add 1.0 factor to the signed operand, so it implicitly casts it to FLOAT, or use CAST (B AS SIGNED), or even swap (B - A) and change the algorithm accordingly.

Answer 3

I had the same problem, it occurred on a JOIN and couldn't figure out what was going on, in the end it was typo in the ON clause where I placed a minus sign instead of an equal sign. Might be stupid but I just didn't see it for about 30 minutes and maybe this could help someone!!!

Answer 4

I don’t quite understand why everyone is saying unsigned. I have a special value in sql and also reported this error. I did it by converting this value to decimal.

cast(1000000000000000000 AS DECIMAL ( 35, 2 ))

Answer 5

I actualy found that question why I was searching for solution. If you have same problem as I do, try disabling "unsigned" parameter.

It is quite possible that your code fails here:

(
quantity - COUNT( game_moblist.spawn_id )
)

because if result of that matematic operation is less than zero it will fail with "unsigned" parameter.

Answer 6

Additional way is to use MySQL IF operator. This helped me when both columns were BIGINT and Unsigned.

Answer 7

I had similar problem, This error also come if our column have 0 value and we try to update it with -1 value.

In my case MySQL query were Failing if column1 is already have value 0, i.e column1 = 0

For example:

UPDATE `table1` SET `column1` = `column1` - 1 WHERE `column2` = XYZ

This give error

BIGINT UNSIGNED value is out of range in ....

To counter this problem

UPDATE `table1`
SET `column1` = (
    CASE WHEN `column1` < 1
    THEN 0
    ELSE (`column1` - 1)
end)
WHERE `column2` = 1
LIMIT 1