BIGINT UNSIGNED value is out of range

Question

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:

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?

Answer 1

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”."