Integer Overflow
A result did not fit its type, so BigQuery stopped the query. INT64, NUMERIC, BIGNUMERIC and FLOAT64 each have their own message, and so do a SUM, a CAST and a date. The fixes are a wider type, a SAFE function or a check before the arithmetic.
Integer Overflow
What BigQuery means
A number has a type, and the type has a range. INT64 runs from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. NUMERIC holds 38 digits and goes up to about 9.9e28. BIGNUMERIC goes up to about 5.8e38. When a result lands outside the range, BigQuery stops the query. Arithmetic never wraps around. A bit shift does; see below.
The error comes when the query runs. A dry run passes, even for SELECT 9223372036854775807 + 1.
There is no one overflow message. The wording depends on the type and on the operation, and the sections below list each one.
The smallest query that reproduces it
SELECT 9223372036854775807 + 1 AS x
Integer Overflow
Do the sum in a wider type:
SELECT CAST(9223372036854775807 AS NUMERIC) + 1 AS x
This returns 9223372036854775808.
INT64 arithmetic
| Operation | Message |
|---|---|
x + 1, x - 1, x * 2, with x at the edge of the range | Integer Overflow |
x * x with x = 3037000500 | Integer Overflow |
ABS(-9223372036854775808) | Integer Overflow |
DIV(-9223372036854775808, -1) | Integer Overflow |
-x with x = -9223372036854775808 | int64 overflow: --9223372036854775808 |
FARM_FINGERPRINT('a') * 1000 | Integer Overflow |
UNIX_SECONDS(TIMESTAMP '2300-01-01') * 1000000000 | Integer Overflow |
The UNIX_SECONDS row works for today’s dates. A date far enough ahead, such as 2300, overflows.
Aggregates
| Query | Message |
|---|---|
SUM(x) over [9223372036854775807, 1] | Error in SUM aggregation: integer overflow |
SUM(x) OVER () over the same values | Error in analytic SUM aggregation: integer overflow |
SUM(x) over two copies of 4611686018427387904 | Error in SUM aggregation: integer overflow |
SUM(x) over NUMERIC values beyond its range | Error in SUM aggregation: numeric overflow |
SUM(x) over BIGNUMERIC values beyond its range | Error in SUM aggregation: bignumeric overflow |
SUM(x) over [1e308, 1e308] | Error in SUM aggregation: floating point overflow |
A sum that goes over and comes back is still an error: [9223372036854775807, 1, -1] fails too.
AVG and STDDEV over the same large INT64 values do not fail. They return a FLOAT64.
NUMERIC, BIGNUMERIC and FLOAT64
| Operation | Message |
|---|---|
NUMERIC '99999999999999999999999999999.999999999' + NUMERIC '1' | numeric overflow: 99999999999999999999999999999.999999999 + 1 |
the same value * 10 | numeric overflow: 99999999999999999999999999999.999999999 * 10 |
CAST(x AS NUMERIC) * x with x = 9223372036854775807 | numeric overflow: 9223372036854775807 * 9223372036854775807 |
POW(CAST(10 AS NUMERIC), 40) | numeric overflow: POW(10, 40) |
a BIGNUMERIC at its largest value + 1 | BIGNUMERIC overflow: 578960446186580977117854925043439539266.34992332820282019728792003956564819967 + 1 |
CAST(9223372036854775807 AS BIGNUMERIC) * 1e30 | BIGNUMERIC overflow: 9223372036854775807 * 1000000000000000000000000000000 |
1e308 * 10 | double overflow: 1e+308 * 10 |
EXP(1000) | Floating point error in function: EXP(1000) |
POW(10, 400) | Floating point error in function: POW(10, 400) |
LOG(0) | Floating point error in function: LN(0) |
The Floating point error wording also covers a number outside a function’s domain: ACOS(2), LN(-1) and POW(0, -1) fail with it. SQRT(-1) says Argument to SQRT cannot be negative: -1. SAFE.SQRT(-1) and SAFE.LOG(0) return NULL.
Conversions
A CAST to a type that is too small gives a message with out of range:
| Query | Message |
|---|---|
CAST(1e19 AS INT64) | int64 out of range: 1e+19 |
CAST(CAST(x AS FLOAT64) AS INT64) with x = 9223372036854775807 | int64 out of range: 9.22337e+18 |
CAST(NUMERIC '99999999999999999999' AS INT64) | int64 out of range: 99999999999999999999 |
CAST(SUM(CAST(x AS NUMERIC)) AS INT64) over [9223372036854775807, 1] | int64 out of range: 9223372036854775808 |
CAST(1e30 AS NUMERIC) | numeric out of range: 1e+30 |
CAST(1e39 AS BIGNUMERIC) | BIGNUMERIC out of range: 1e+39 |
CAST(CAST('inf' AS FLOAT64) AS INT64) | Illegal conversion of non-finite floating point number to an integer: inf |
A CAST of text has its own messages:
| Query | Message |
|---|---|
CAST('9223372036854775808' AS INT64) | Bad int64 value: 9223372036854775808 |
CAST('abc' AS FLOAT64) | Bad double value: abc |
CAST('99999999999999999999999999999999' AS NUMERIC) | Invalid NUMERIC value: 99999999999999999999999999999999 |
PARSE_NUMERIC('1e30') | Invalid input to PARSE_NUMERIC: "1e30" |
CAST('99999999999999999999999999999999' AS BIGNUMERIC) works. See Bad int64 value for the text forms.
Literals
A number written in the query that is already too big fails when the query is compiled, so a dry run shows it:
SELECT 9223372036854775808 AS a
Invalid integer literal: 9223372036854775808 at [1:8]
SAFE_CAST(9223372036854775808 AS INT64) gives the same error, because the literal is read before the SAFE_CAST runs. Write it as NUMERIC '9223372036854775808', which runs. NUMERIC '1e30' gives Invalid NUMERIC literal: "1e30" at [1:8]. A literal -9223372036854775808 is fine.
Dates and times
| Query | Message |
|---|---|
TIMESTAMP_ADD(TIMESTAMP '9999-12-31 23:59:59', INTERVAL 1 SECOND) | Adding 1 SECOND to timestamp 9999-12-31 23:59:59.000000+00 causes overflow |
TIMESTAMP_ADD(TIMESTAMP '2020-01-01', INTERVAL 9223372036854775807 DAY) | Adding 9223372036854775807 DAY to timestamp 2020-01-01 00:00:00.000000+00 causes overflow |
DATE_ADD(DATE '2020-01-01', INTERVAL 99999999 YEAR) | Adding 99999999 YEAR to date 2020-01-01 causes overflow |
DATE_ADD(DATE '9999-12-31', INTERVAL 1 DAY) | Overflow in DATE + INT64 operator. |
TIMESTAMP_SECONDS(9223372036854775807), and TIMESTAMP_MICROS and TIMESTAMP_MILLIS | TIMESTAMP value is out of allowed range: from 0001-01-01 00:00:00.000000+00 to 9999-12-31 23:59:59.999999+00. |
INTERVAL 9223372036854775807 DAY | Interval field days '9223372036854775807' is out of range -3660000 to 3660000; error in INTERVAL expression |
Fixes
Use a wider type. NUMERIC and BIGNUMERIC hold bigger integers, and FLOAT64 holds much bigger ones with less precision. Cast before the arithmetic. A cast after it is too late.
| Query | Result |
|---|---|
CAST(x AS NUMERIC) + 1 with x = 9223372036854775807 | 9223372036854775808 |
SUM(CAST(x AS NUMERIC)) over [9223372036854775807, 9223372036854775807] | 18446744073709551614 |
SUM(CAST(x AS BIGNUMERIC)) over three copies of 9223372036854775807 | 27670116110564327421 |
CAST(x AS BIGNUMERIC) * x with x = 9223372036854775807 | 85070591730234615847396907784232501249 |
CAST(x AS FLOAT64) + 1 | 9.223372036854776E18 |
CAST(UNIX_SECONDS(TIMESTAMP '2300-01-01') AS NUMERIC) * 1000000000 | 10413792000000000000 |
The FLOAT64 result is rounded, and it does not fit back in an INT64.
Return NULL instead of an error. SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY and SAFE_NEGATE return NULL on overflow. All four returned NULL for the edge values. SAFE_CAST does the same for a conversion:
SELECT SAFE_CAST(1e19 AS INT64) AS a,
SAFE_CAST('9223372036854775808' AS INT64) AS b,
SAFE_CAST(1e30 AS NUMERIC) AS c,
SAFE.EXP(1000) AS d
All four columns are NULL. SAFE_CAST(SUM(CAST(x AS NUMERIC)) AS INT64) is NULL when the sum is too big for an INT64.
A NULL that replaces an overflow hides it. To keep the value as it was, test for it:
SELECT IFNULL(SAFE_ADD(big, one), big) AS capped, SAFE_ADD(big, one) IS NULL AS overflowed
For big = 9223372036854775807 and one = 1 this returns 9223372036854775807 and true.
Find the rows that overflow. SELECT x FROM t WHERE SAFE_ADD(x, 1) IS NULL returns only the row with 9223372036854775807 from [9223372036854775807, 1, 2]. To sum values that do not fit in an INT64, use SUM(CAST(x AS NUMERIC)).
Queries that look wrong but run
Division of the smallest INT64 by -1. x / -1 returns a FLOAT64, 9.223372036854776E18, with no error. MOD(x, -1) returns 0. DIV(x, -1) is the one that overflows.
Rounding at the edge. ROUND(9223372036854775807, -1), TRUNC(...), CEIL(...) and FLOOR(...) all returned 9.223372036854776E18, a FLOAT64.
Shifts. 9223372036854775807 << 1 returns -2 and 1 << 70 returns 0. A shift wraps and does not raise an error.
Large values near the edge. x * x with x = 3037000499 returns 9223372030926249001. One more and it overflows.
A range of large values. GENERATE_ARRAY(9223372036854775806, 9223372036854775807) returns both values.
Different errors that look similar
| Message | Cause |
|---|---|
division by zero: 10 / 0 | See Division by zero |
Bad double value: abc | text that is not a number |
In Querylab.io
The editor does not mark a number that is over the INT64 range. SELECT 9223372036854775808 and NUMERIC '1e30' pass the editor, and BigQuery’s dry run reports Invalid integer literal and Invalid NUMERIC literal when you pause typing. An overflow in arithmetic, a SUM or a CAST shows only when the query runs, because it depends on the values.
There is no fix button. The error bar links to this page. Which type to widen to, or whether a NULL is acceptable, is your decision.
See SAFE functions, Data types and Casting and conversion.