Docs/Error messages/Integer Overflow
Error messagesData values

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

OperationMessage
x + 1, x - 1, x * 2, with x at the edge of the rangeInteger Overflow
x * x with x = 3037000500Integer Overflow
ABS(-9223372036854775808)Integer Overflow
DIV(-9223372036854775808, -1)Integer Overflow
-x with x = -9223372036854775808int64 overflow: --9223372036854775808
FARM_FINGERPRINT('a') * 1000Integer Overflow
UNIX_SECONDS(TIMESTAMP '2300-01-01') * 1000000000Integer Overflow

The UNIX_SECONDS row works for today’s dates. A date far enough ahead, such as 2300, overflows.

Aggregates

QueryMessage
SUM(x) over [9223372036854775807, 1]Error in SUM aggregation: integer overflow
SUM(x) OVER () over the same valuesError in analytic SUM aggregation: integer overflow
SUM(x) over two copies of 4611686018427387904Error in SUM aggregation: integer overflow
SUM(x) over NUMERIC values beyond its rangeError in SUM aggregation: numeric overflow
SUM(x) over BIGNUMERIC values beyond its rangeError 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

OperationMessage
NUMERIC '99999999999999999999999999999.999999999' + NUMERIC '1'numeric overflow: 99999999999999999999999999999.999999999 + 1
the same value * 10numeric overflow: 99999999999999999999999999999.999999999 * 10
CAST(x AS NUMERIC) * x with x = 9223372036854775807numeric overflow: 9223372036854775807 * 9223372036854775807
POW(CAST(10 AS NUMERIC), 40)numeric overflow: POW(10, 40)
a BIGNUMERIC at its largest value + 1BIGNUMERIC overflow: 578960446186580977117854925043439539266.34992332820282019728792003956564819967 + 1
CAST(9223372036854775807 AS BIGNUMERIC) * 1e30BIGNUMERIC overflow: 9223372036854775807 * 1000000000000000000000000000000
1e308 * 10double 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:

QueryMessage
CAST(1e19 AS INT64)int64 out of range: 1e+19
CAST(CAST(x AS FLOAT64) AS INT64) with x = 9223372036854775807int64 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:

QueryMessage
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

QueryMessage
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_MILLISTIMESTAMP 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 DAYInterval 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.

QueryResult
CAST(x AS NUMERIC) + 1 with x = 92233720368547758079223372036854775808
SUM(CAST(x AS NUMERIC)) over [9223372036854775807, 9223372036854775807]18446744073709551614
SUM(CAST(x AS BIGNUMERIC)) over three copies of 922337203685477580727670116110564327421
CAST(x AS BIGNUMERIC) * x with x = 922337203685477580785070591730234615847396907784232501249
CAST(x AS FLOAT64) + 19.223372036854776E18
CAST(UNIX_SECONDS(TIMESTAMP '2300-01-01') AS NUMERIC) * 100000000010413792000000000000

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

MessageCause
division by zero: 10 / 0See Division by zero
Bad double value: abctext 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.