CODEWITHSUNDEEP
mysql
Shawn
Shawn

Reputation: 911

BIGINT UNSIGNED VALUE IS out of range My SQL

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 for 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?

Upvotes: 38

Views: 82744

Answers (10)

Remade
Remade

Reputation: 427

In my case, I was subtracting two columns that were UNSIGNED and the result of some of the subtractions was negative. Setting both columns to SIGNED INT resolved it for me but this is just a temporary fix

Upvotes: 0

hu wentao
hu wentao

Reputation: 131

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 ))

Upvotes: 4

Rana.Asif
Rana.Asif

Reputation: 443

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

Upvotes: 2

kklepper
kklepper

Reputation: 813

sql_mode worked in the MySQL client and Adminer, but not in CodeIgniter, where it counts. Casting didn't help either.

A simple arithmetic operation did the trick:

error

id - id_ex*1000000000 = id_sm

works

id_sm + id_ex*1000000000 = id

Upvotes: 0

Fabien Haddadi
Fabien Haddadi

Reputation: 2080

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.

Upvotes: 20

zwadder
zwadder

Reputation: 41

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!!!

Upvotes: 4

Philippe
Philippe

Reputation: 1

Another possible cause seems to be how the type of the result variable is allocated.

eg.

mysql> select (total_balance_06 * 0.045/(1-(1/1.045)^term_06) + unsec_instalments)/income from tbl_EUR_PDH;

fails with

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(1 - ((1 / 1.045) ^ markov.tbl_EUR_PDH.term_06))'

whereas

mysql> select (total_balance_06 * 0.045/(1.0-(1.0/1.045)^term_06) + unsec_instalments)/income from tbl_EUR_PDH;

does what one would expect (note that I simply replace "1" by "1.0")

Philippe

Upvotes: 0

Andron
Andron

Reputation: 6631

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

Upvotes: 1

Gonzi
Gonzi

Reputation: 87

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.

Upvotes: 2

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

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.

Upvotes: 53

Related Questions