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?

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

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)

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

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.

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

maybe you can use cast

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

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.