ANSI mode by default
Spark 4.0 turns ANSI SQL on: overflows and invalid casts now raise errors instead of returning null.
On this page
Show code in
Every code block on the page follows this.
You will learn
- What ANSI mode changes, with concrete examples
- Why Spark 4.0 turned it on by default
- The try_ functions that keep the old forgiving behaviour
- How to migrate jobs safely
spark.sql.ansi.enabled = true, the default in Spark 4.0, invalid operations raise errors instead of silently returning null or wrapping around: bad casts, division by zero, integer overflow, out-of-range array access. Use try_cast, try_divide and friends where null is the intended result.What changes
| Expression | Spark 3 default (non-ANSI) | Spark 4.0 default (ANSI) |
|---|---|---|
CAST('abc' AS INT) | null | Error: CAST_INVALID_INPUT |
10 / 0 | null | Error: DIVIDE_BY_ZERO |
2147483647 + 1 (INT) | -2147483648 (wraps around) | Error: ARITHMETIC_OVERFLOW |
array(1, 2)[5] | null | Error: INVALID_ARRAY_INDEX |
CAST('2025-02-30' AS DATE) | null | Error |
Type coercion also becomes stricter: implicit casts that could lose information, such as comparing a string to a number by casting the number, follow SQL-standard rules instead of Spark's permissive ones.
Why the default changed
Silent nulls are data-quality bugs that surface weeks later in a dashboard. A cast that fails loudly at load time is cheaper than an average computed over silently dropped values. ANSI mode also makes Spark SQL behave like other SQL databases, which matters for migrations and for anyone who learned SQL elsewhere.
The try_ functions
When null really is the desired result, say so explicitly:
| Function | Returns null instead of failing on |
|---|---|
try_cast(x AS type) | Invalid input |
try_divide(a, b) | Division by zero |
try_add, try_subtract, try_multiply | Overflow |
try_element_at(arr, i) | Out-of-range index |
try_to_timestamp, try_to_number | Unparseable input |
from pyspark.sql import functions as F clean = raw.withColumn("amount", F.expr("try_cast(amount_str AS DECIMAL(12,2))")) bad = clean.filter(F.col("amount").isNull() & F.col("amount_str").isNotNull()) print(bad.count(), "rows could not be parsed")
SELECT try_cast(amount_str AS DECIMAL(12,2)) AS amount, try_divide(revenue, orders) AS avg_order FROM raw
Migrating a job to Spark 4.0
- 1Run the job on Spark 4.0 in a test environment and collect the errors. Each error names its class and usually the failing value.
- 2For each one, decide: is the data bad (fix upstream, or quarantine the rows) or is null the right answer (use the matching
try_function)? - 3Only as a temporary measure, set
spark.sql.ansi.enabled=falsefor one job to keep Spark 3 behaviour while you fix it.
Common mistakes
Turning ANSI off globally after upgrading
Wrapping everything in try_cast
Ignoring integer overflow
Key takeaways
- Spark 4.0 enables ANSI mode by default.
- Invalid casts, division by zero, overflow and bad indexes now raise errors.
- try_cast, try_divide and other try_ functions return null on purpose.
- Disable ANSI only temporarily while migrating.
Check yourself
3 questions1. In Spark 4.0 defaults, what does CAST('12x' AS INT) do?
Show the answer
Raises an error. ANSI mode raises CAST_INVALID_INPUT for invalid casts.
2. Which keeps returning null for a bad value with ANSI on?
Show the answer
try_cast. try_cast is the explicit, tolerant version.
3. What did INT overflow do in Spark 3 by default?
Show the answer
Wrapped around silently. Without ANSI, integer arithmetic wrapped around like Java ints.
Go deeper
Primary sources: ANSI compliance · Migration guide