2

I suddenly experienced a query on my production site returning no results but also producing no errors, it was just returning zero rows. Come to find out a subquery was producing the below error:

Error Code: 1690. BIGINT UNSIGNED value is out of range in '(s.subscriber_count - (s.subscribed_count - s.unsubscribed_count))'

My data set looks like:

enter image description here

I came across https://stackoverflow.com/a/11780905/197606 and haven't actually tried it because I want to understand why this is producing this error. One comment mentions a negative expression, but there's no scenario in my situation where unsubscribed_count is less than subscribed_count.

Can anyone help me understand why I'm seeing this error?

  • Do you get any result from SELECT * FROM table_name WHERE subscribed_count < unsubscribed_count ; – ypercubeᵀᴹ Jul 21 '18 at 9:57
  • Please also edit the question and add: the CREATE TABLE statement, the actual query and the version of MySQL. – ypercubeᵀᴹ Jul 21 '18 at 9:59
5

The subtraction (-) causes the error. It is pessimistic. You are stuck with using CAST(... AS SIGNED) to avoid the error.

SHOW VARIABLES LIKE 'sql_mode';

See if it includes NO_UNSIGNED_SUBTRACTION

For 'rationale', it may be best to quote some (of many) bug reports on the topic:

https://bugs.mysql.com/bug.php?id=61410 :

This is intentional change in behavior in 5.5. For those who needs this kind of statements to work and produce meaningful (unlike in pre-5.5) results, there is NO_UNSIGNED_SUBTRACTION SQL mode (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtractio...).

https://bugs.mysql.com/bug.php?id=46799 :

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, it becomes the maximum integer value. If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative.

https://bugs.mysql.com/bug.php?id=21070 :

"Warning: When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 12.8, “Cast Functions and Operators”."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.