46

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?

Joyce Babu
  • 19,602
  • 13
  • 62
  • 97
  • Give us an example of the data saved in date, I suspect it has something to do with that. – Khez Apr 09 '11 at 12:44
  • 1
    I'd guess Khez is right and the problem is some value of date that is greater than 1301980250. – Brian Hooper Apr 09 '11 at 13:09
  • I still doubt it can lead to that specific error, but it's still a point to start to replicate the error. – Khez Apr 09 '11 at 13:37
  • 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. – Joyce Babu Apr 09 '11 at 13:47
  • Hmz, Does it also have a limit ? Can you give us the table definition? do SHOW CREATE TABLE news_articles – Khez Apr 09 '11 at 13:49

7 Answers7

87

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
ab5tract
  • 1,098
  • 8
  • 7
  • Weird indeed! log() is not defined for negative numbers! – Jasen Jul 22 '18 at 03:04
  • @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. – Fabien Haddadi Jul 25 '18 at 03:43
  • Its work, thanks, I did not know that must to use CAST (my_var AS SIGNED)) when there are subtractions – Sergio Perez Jan 22 '19 at 08:27
17

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)
Joyce Babu
  • 19,602
  • 13
  • 62
  • 97
  • 14
    @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. – Joyce Babu Apr 13 '11 at 12:00
  • 6
    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';` – Jérôme Apr 17 '14 at 13:12
  • 1
    @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. – Luke Jul 24 '15 at 14:12
  • The "NO_UNSIGNED_SUBTRACTION" did not solve the problem for me. – Erel Segal-Halevi Nov 30 '21 at 09:15
8

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

Ian Chadwick
  • 1,547
  • 1
  • 19
  • 21
4

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
Fabien Haddadi
  • 1,814
  • 17
  • 22
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.

wallyk
  • 56,922
  • 16
  • 83
  • 148
3

maybe you can use cast

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

fairjm
  • 1,115
  • 12
  • 26
1

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.

kojow7
  • 10,308
  • 17
  • 80
  • 135