CODEWITHSUNDEEP
mysqlbiginteger
Joyce Babu
Joyce Babu

Reputation: 20664

BIGINT UNSIGNED value is out of range

I am getting the error

BIGINT UNSIGNED value is out of range in '(1301980250 - mydb.news_articles.date)'

When I run the query 

SELECT *, ((1 / log(1301980250 - date)) * 175) as weight FROM news_articles ORDER BY weight;

Removing the ORDER BY condition, removes the error too. How can I fix it?

Update: The date field contains unix timestamp (ex: 1298944082). The error started appearing after I upgraded MySQL from 5.0.x to 5.5.x

Any help please?

Upvotes: 46

Views: 70294

Answers (7)

kojow7
kojow7

Reputation: 11394

I just came across this issue doing an update on a field where the result ended up being less than 0.

Solution: Verify that none of your updates cause your result to be less than 0 on an unsigned field.

Upvotes: 1

Fabien Haddadi
Fabien Haddadi

Reputation: 2080

Nobody mentionned that the log() function is only defined for strictly positive arguments. Watch for this when using substractions inside of log().

As for the original question, a key factor for resolution was to tell us the data type for the date column. If it is UNSIGNED, MySQL might not like it.

The rule is that MySQL has a poor arithmetic algo, and can't figure out how to substract an operand B FROM another A (= do A-B) when A is coded on less bytes than B AND B > A.

e.g. A = 12 and is SMALLINT, B = 13 AS INT, then MySQL can't figure out what A-B is (-1 !)

To make MySQL content, just expand the coding length of operand A. How? Using CAST(), or multiplying A by a decimal number.

As one can see, it is less a problem of overflow than a problem of handling the sign in the arithmetics of MySQL. A microprocessor, or better, a human, has no problems to perform this kind of arithmetics...

Using CAST() is the way, or for short, just provoke the implicit cast by multiplying operand A by 1. (or 1.0):

e.g

1.*A - B

Upvotes: 4

Ian Chadwick
Ian Chadwick

Reputation: 1627

This can sometimes be caused by nulls in the data.

Use IFNULL to set a default value (probably 0 for a timestamp is a poor default and actually in this case you might be better off excluding and null dates in the WHERE clause)

SELECT (123456 - IFNULL(date, 0)) AS leVar

Upvotes: 8

fairjm
fairjm

Reputation: 1187

maybe you can use cast

SELECT *, ((1 / log(1301980250 - cast(date AS SIGNED))) * 175) as weight FROM news_articles ORDER BY weight;

Upvotes: 3

ab5tract
ab5tract

Reputation: 1108

I recently ran into this and found the most reasonable solution to simply cast any UNSIGNED ints as SIGNED. 

 SELECT *, ((1 / log(1301980250 - cast(date as signed)) * 175) as weight FROM news_articles ORDER BY weight

Upvotes: 88

Joyce Babu
Joyce Babu

Reputation: 20664

The problem was caused by unsigned integer overflow as suggested by wallyk. It can be solved by

  1. using SELECT *, ((1 / log((date - 1301980250) * -1)) * 175) as weight FROM news_articles ORDER BY weight; (This one worked for me) `
  2. Changing sql_mode parameter in my.cnf to NO_UNSIGNED_SUBTRACTION (haven't checked this)

Upvotes: 17

wallyk
wallyk

Reputation: 57774

Any date value after 2011-04-04 22:10:50 PDT (2011-04-05 05:10:50 utc) will cause this error since that would make the expression negative.

Upvotes: 4

Related Questions