ANSI mode and silent NULLs

Spark 3 did not fail on a bad cast. It returned NULL. Spark 4 has ANSI mode on by default, so an invalid cast raises an error. A job that ran for years may suddenly fail because one row in the data was always silently turned into NULL.

Same query, two behaviours

SELECT CAST('12x' AS INT);        -- Spark 3: NULL      Spark 4 (ANSI): error
SELECT CAST(2147483648 AS INT);   -- Spark 3: wraps around to a wrong number
                                  -- Spark 4 (ANSI): overflow error
SELECT 1 / 0;                     -- Spark 3: NULL      Spark 4 (ANSI): divide by zero error

The old behaviour hid data loss. If amount came in as '12x', the row quietly got amount = NULL, and revenue was understated, with nobody told. Under ANSI, the job fails and you see the bad value in the error message.

Why it is painful in a migration

The failure is correct, but it appears in code that nobody touched. Several things might be behind it: dirty source data that was always there, a cast written assuming NULL on failure, or a join key that was cast from string to integer.

How to handle it

If you want tolerant parsing on purpose, say so in the code with the try_ functions:

SELECT try_cast('12x' AS INT);    -- NULL, on purpose
SELECT try_divide(a, b) FROM t;   -- NULL when b = 0

df.select(F.expr("try_cast(amount_str AS DECIMAL(12,2))").alias("amount"))

Then handle the NULLs explicitly. Count them, send those rows to a quarantine table, and alert on the rate. That is better than the old silent behaviour, because the tolerance is visible in the code.

Migration plan

• Run the test suite and a sample of production data against Spark 4 with ANSI on, and collect the failures.
• Fix real data problems at the source, or add try_cast plus checks where tolerance is intended.
• If you need time, set spark.sql.ansi.enabled=false as a temporary bridge, and plan to remove it.

The balance

ANSI is good for data quality, and uncomfortable for migrations. The answer interviewers like is that you would not just switch it off. You would use it to find hidden bad data.