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

very similar to https://stackoverflow.com/questions/5605085/bigint-unsigned-value-is-out-of-range — qdinar, Jul 13 '17 at 09:35

score 51 · Accepted Answer · answered Jul 28 '12 at 19:54

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.

The main point to note from the link is: `Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default.` — arun, Oct 28 '14 at 01:40
In case it was not 100% clear: `SELECT (CAST(1 AS UNSIGNED) - 2);` Also triggers the error just at the other end of the range. Something to keep in mind if you use unsigned Ints and have queries that include subtraction of values. If any result turns negative the query fails. — MerlinTheMagic, Jun 15 '21 at 08:13

Fabien Haddadi · Answer 2 · 2018-04-09T03:05:09.437

14

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.

edited Apr 09 '18 at 03:05

answered Jan 17 '17 at 03:13

Fabien Haddadi

1,814
17
22

This worked for me. A-B didn't work, but B-A worked like a charm! – Pirate X May 05 '20 at 23:20
Oh Great Zeus, thank you. I couldn't for the LIFE of me figure out why my case/when statement was causing my query to break when I was doing math with the result. Wrapping "CAST(BLAH AS SIGNED)" around it worked very well. – Kenny Wyland Jul 21 '21 at 23:06

score 4 · Answer 3 · answered Mar 08 '15 at 10:17

4

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

answered Mar 08 '15 at 10:17

zwadder

41
1

1

While this does not provide a direct answer to the question, it does help others who may arrive at the same problem (as I did). After all, the Minus sign is right next to the equal sign! ;) +1 – Sablefoste Jan 03 '16 at 05:12

score 4 · Answer 4 · answered Mar 12 '20 at 05:36

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

answered Mar 12 '20 at 05:36

hu wentao

131
1
5

score 2 · Answer 5 · answered Feb 07 '13 at 16:58

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.

score 2 · Answer 6 · edited Mar 05 '20 at 04:18

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

score 1 · Answer 7 · answered Mar 25 '13 at 16:06

1

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

answered Mar 25 '13 at 16:06

Andron

6,413
4
43
56

score 0 · Answer 8 · answered Dec 11 '13 at 12:56

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

score 0 · Answer 9 · edited Jun 20 '20 at 09:12

0

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 17 '18 at 20:15

kklepper

763
8
13

score 0 · Answer 10 · answered Jul 08 '21 at 22:43

0

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

answered Jul 08 '21 at 22:43

Remade

388
5
13

BIGINT UNSIGNED VALUE IS out of range My SQL

10 Answers10

error

works

Linked