Home / Spark / Query compilation
Spark: [DIVIDE_BY_ZERO] Division by zero. Use try_divide to tolerate divisor being 0
org.apache.spark.SparkArithmeticException[DIVIDE_BY_ZERO] Division by zero. Use `try_divide` to tolerate divisor being 0 and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.ANSI mode is on, which is the default since Spark 4.0, so dividing by zero raises an error instead of returning NULL. Queries that worked on Spark 3 start failing after an upgrade.
Common causes
- A divisor column contains 0, and the query relied on the pre-ANSI behavior of returning NULL.How to confirm: Run SELECT COUNT(*) FROM t WHERE divisor = 0 on the input.
How to fix it
Use try_divide, or guard the divisor with NULLIF.
SELECT try_divide(orders, visits) AS conversion FROM daily_stats;
-- or
SELECT orders / NULLIF(visits, 0) AS conversion FROM daily_stats;As a temporary measure, turn ANSI mode off for the session; this also changes overflow and cast behavior.
| Setting | Default | Note |
|---|---|---|
spark.sql.ansi.enabled | true | Spark 4.0 and later |
Where the message comes from
error-conditions.jsoninapache/sparkatv4.1.3- Message checked in Spark 4.1.3 (error condition DIVIDE_BY_ZERO)
Message template in the source: Division by zero. Use `try_divide` to tolerate divisor being 0 and return NULL instead. If necessary set <config> to "false" to bypass this error.