BIGINT UNSIGNED value is out of range

Question

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?

Solution 1

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

Solution 2

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)

Solution 3

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

Solution 4

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.

Solution 5

maybe you can use cast

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

View more solutions

Comments

[Khez]

Give us an example of the data saved in date, I suspect it has something to do with that.

[Brian Hooper]

I'd guess Khez is right and the problem is some value of date that is greater than 1301980250.

[Khez]

I still doubt it can lead to that specific error, but it's still a point to start to replicate the error.

[Joyce Babu]

I am sorry, I did not copy the code fully. The query had a ORDER BY on weight(I have updated the original post). The error appears only when it is present.

[Khez]

Hmz, Does it also have a limit ? Can you give us the table definition? do SHOW CREATE TABLE news_articles

[Joyce Babu]

@wallyk - Answering my own question does not give me any points, so I did not do it for improving my reputation. I found the answer at MySQL IRC. I do appreciate your time and effort and have upvoted your reply. But you did not provide the solution to the problem and I wanted to share it here.

[Jérôme]

If you can't or don't want to modify the configuration file. You can also use the following statement : SET sql_mode='NO_UNSIGNED_SUBTRACTION';

[Luke]

@wallyk and anyone else reading this now - this is simply not true. You can answer your own question at the point of asking it. Stackoverflow wants good questions with well documented answers, it doesn't matter who provides it.

[Vael Victus]

In my case, I was doing a value = value-1000 on a 0 value, in an unsigned int column.

[Jasen]

Weird indeed! log() is not defined for negative numbers!

[Fabien Haddadi]

@Jasen : indeed, it is a mathematical theorem that the log is not defined for negative numbers. Demonstration: log is the reverse function to "power of 10", but all powers of 10 are strictly positive, by definition of power. Therefore there does not exist any real number R such as 10^(R) <= 0.

[Sergio Perez]

Its work, thanks, I did not know that must to use CAST (my_var AS SIGNED)) when there are subtractions

[Erel Segal-Halevi]

The "NO_UNSIGNED_SUBTRACTION" did not solve the problem for me.