division by zero: X / 0

The / operator fails the whole query when one row has a divisor of 0. The message shows the numbers of the failing row. SAFE_DIVIDE and NULLIF turn the failure into a NULL, IEEE_DIVIDE returns infinity, and a WHERE on the divisor drops the row.

division by zero: 20 / 0

What BigQuery means

One row had a divisor of 0, and BigQuery does not guess what the result should be. It stops the whole query, so one bad row hides all the good ones. This is a runtime error. The query compiles and the dry run passes, because BigQuery cannot know the data until it reads it.

The numbers in the message are the values of the failing row, not the text of your expression. 20 / 0 means a row where the left side was 20 and the right side was 0. It does not name the column or give a position, so you search for the division yourself.

In a script, the message starts with Query error: and ends with the position of the statement that failed, not of the division: Query error: division by zero: 10 / 0 at [2:1].

The smallest query that reproduces it

WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT a / b AS ratio
FROM t

division by zero: 10 / 0

A divisor that is a constant 0 fails the same way: SELECT 1 / 0 gives division by zero: 1 / 0.

The fixes

Take this table, where one row has b = 0:

WITH t AS (
  SELECT 10 AS a, 2 AS b
  UNION ALL SELECT 20, 0
  UNION ALL SELECT 30, 5
)
SELECT a / b AS ratio
FROM t

division by zero: 20 / 0

Find the rows first. The query below returns them:

SELECT a, b
FROM t
WHERE b = 0

SAFE_DIVIDE returns NULL for those rows. The other rows keep their values:

SELECT a, SAFE_DIVIDE(a, b) AS ratio
FROM t
ORDER BY a

The ratio is 5.0, NULL and 6.0.

NULLIF does the same with the operator. NULLIF(b, 0) turns a zero into NULL, and dividing by NULL gives NULL:

SELECT a, a / NULLIF(b, 0) AS ratio
FROM t
ORDER BY a

Use NULLIF when the divisor is a long expression and you want the division to stay readable.

Show 0 instead of NULL. Wrap the result in COALESCE:

SELECT a, COALESCE(SAFE_DIVIDE(a, b), 0) AS ratio
FROM t
ORDER BY a

Be careful with this one. A ratio of 0 and a ratio that does not exist are different facts, and the 0 will be averaged with the real values.

Leave the rows out. Filter on the divisor:

SELECT a, a / b AS ratio
FROM t
WHERE b != 0
ORDER BY a

The rows with b = 0 are gone from the result. WHERE b != 0 AND a / b > 1 happened to run here, but BigQuery documents that “the order of evaluation of operands to AND and OR can vary, and evaluation can be skipped if unnecessary.” Use CASE, IF or SAFE_DIVIDE instead.

A conditional expression skips the division. CASE, IF and COALESCE evaluate left to right and only evaluate the branch they pick:

WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT CASE WHEN b != 0 THEN a / b END AS ratio
FROM t

Aggregates divide too. SUM(a) / SUM(b) fails when the sum in the divisor is 0:

WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT SUM(a) / SUM(b) AS ratio
FROM t

division by zero: 10 / 0

WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT SAFE_DIVIDE(SUM(a), SUM(b)) AS ratio
FROM t

INT64 and FLOAT64

The type of the numbers makes no difference. Dividing by 0 and by 0.0 both fail, and so does NUMERIC. SELECT 10.5 / 0.0 gives division by zero: 10.5 / 0. The message prints the divisor as 0 in both cases.

Other languages return infinity for a float divided by zero. BigQuery does that only through IEEE_DIVIDE:

SELECT a, IEEE_DIVIDE(a, b) AS ratio
FROM t
ORDER BY a

This returns 5.0, Infinity and 6.0. IEEE_DIVIDE(-10, 0) is -Infinity and IEEE_DIVIDE(0, 0) is NaN. Infinity and NaN are real FLOAT64 values that move through sums and comparisons, so use it when you want IEEE behavior and not as a quiet replacement for NULL.

The variants of the message

Each line was produced by BigQuery:

division by zero: 20 / 0
division by zero: 10.5 / 0
division by zero: MOD(10, 0)
Query error: division by zero: 10 / 0 at [2:1]

DIV(10, 0), the integer division function, gives division by zero: 10 / 0, worded like the operator. MOD(10, 0) gives division by zero: MOD(10, 0). For both, SAFE.DIV(10, 0) and SAFE.MOD(10, 0) return NULL. SAFE_DIVIDE does not replace MOD.

The Query error: form comes from a script whose second statement fails:

CREATE TEMP TABLE t AS SELECT 10 AS a, 0 AS b;
SELECT a / b AS ratio FROM t;

Queries that look wrong but run

A NULL divisor. SELECT 10 / CAST(NULL AS INT64) returns NULL. Only a real 0 fails.

A division in a query with no rows. WHERE FALSE or an empty table never evaluates the division, so nothing fails:

WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT a / b AS ratio
FROM t
WHERE FALSE

A zero numerator. SELECT 0 / 5 returns 0.0. Only the divisor matters.

Different errors that look like this one

double overflow: 1e+308 / 1e-308. The divisor is not 0 but tiny, and the result does not fit in a FLOAT64:

WITH t AS (SELECT 1e308 AS a, 1e-308 AS b)
SELECT a / b AS ratio
FROM t

SAFE_DIVIDE(a, b) returns NULL for this query.

In Querylab.io

The editor does not mark this error, because it depends on the data. It comes back from BigQuery when the query runs.

After a failed run of a single query, the status bar offers fixes when the query has one division that can fail:

• Return NULL when dividing by zero rewrites a / b to SAFE_DIVIDE(a, b). When the division is in a condition such as WHERE, the button reads Skip rows where the divisor is 0 (SAFE_DIVIDE).
• Show rows where the divisor is 0 runs a query that lists them.
• Skip rows where the divisor is 0 adds (b IS NULL OR b != 0) to the WHERE.

The last two appear for a plain row-by-row SELECT, without GROUP BY, aggregates or window functions, and with the division in the select list. No fix is offered for MOD, for an error from inside a script, or when the query reads a view or calls a stored function, because the division that failed may be hidden there.