Does Spark use ANSI SQL mode by default now?

Summary

• Yes. Since Apache Spark 4.0 (released May 23, 2025), spark.sql.ansi.enabled defaults to true, so Spark SQL uses an ANSI-compliant dialect instead of Hive-compatible behavior. Spark 4.2, the current release, keeps that default.
• In ANSI mode, invalid operations raise a runtime error where Spark 3.x returned NULL or a wrapped-around value: integer overflow, malformed casts such as CAST('a' AS INT), division by zero and out-of-range array indexes.
• The Spark 4.0 migration guide documents two ways to restore the old behavior: set spark.sql.ansi.enabled to false, or set the SPARK_ANSI_SQL_MODE environment variable to false.
• When NULL on bad input is the result you want, wrap that expression in try_cast, try_divide, try_add or another try_* function and leave ANSI mode on for the rest of the job.
• On Databricks, ANSI mode is on by default in Databricks Runtime 17.0 and above, and serverless notebooks and jobs default spark.sql.ansi.enabled to true.

Does Spark use ANSI SQL mode by default now?

Yes. Apache Spark turned ANSI SQL mode on by default in Spark 4.0 (SPARK-44444, "Use ANSI SQL mode by default"), and Spark 4.2, the latest release as of October 2026, keeps it on. The switch is spark.sql.ansi.enabled. The setting has existed since Spark 3.0.0, but it stayed off by default until 4.0, so Spark 3.x jobs ran on the lenient, Hive-compatible dialect unless someone opted in. That's the real upgrade risk: expressions that quietly returned NULL on Spark 3.5 now stop the query with an error.

What changes when ANSI mode is on

The Spark docs put it in one sentence: Spark "will throw an exception at runtime instead of returning null results if the inputs to a SQL operator/function are invalid." ANSI mode also switches type coercion to rules that are "consistently based on data type precedence," and it disallows a few casts outright.

Behavior	ANSI mode on (Spark 4.x default)	ANSI mode off (legacy)
Integer overflow, such as 2147483647 + 1	Fails with ARITHMETIC_OVERFLOW	Wraps around as in Java or Scala; decimal overflow returns NULL
Malformed cast, such as CAST('a' AS INT)	Fails with CAST_INVALID_INPUT	Returns NULL
Division by zero	Fails; try_divide returns NULL instead	Returns NULL
element_at with an invalid array index	Throws ArrayIndexOutOfBoundsException	Returns NULL
Casts between numeric and binary, between date or timestamp and boolean, and from date to numeric	Not allowed	Allowed
Reserved keywords used as identifiers	Still allowed unless spark.sql.ansi.enforceReservedKeywords is also true (default false)	Allowed

The error messages point at the fix. The CAST_INVALID_INPUT message itself says to "Use try_cast to tolerate malformed input and return NULL instead," and the overflow error suggests try_add.

How to check the setting, tolerate bad input, or opt out

SET with a key returns that key's value. Each try_* function below returns NULL where the plain operator would fail:

SET spark.sql.ansi.enabled;
SELECT try_cast('a' AS INT), try_divide(1, 0), try_add(2147483647, 1);

To opt out for a single session from PySpark:

spark.conf.get("spark.sql.ansi.enabled")           # 'true' on Spark 4.x
spark.conf.set("spark.sql.ansi.enabled", "false")  # this session only

The migration guide also documents an environment variable for restoring the previous behavior:

export SPARK_ANSI_SQL_MODE=false

Our recommendation: keep ANSI mode on. A global opt-out brings back silent NULLs and wrapped values in every query, which are the data problems ANSI mode exists to surface. Treat false as a short bridge while you fix the specific expressions, and use try_* functions where NULL-on-error is the semantics you want.
Two related settings behave differently. spark.sql.storeAssignmentPolicy controls implicit casts when you insert into a table. It defaults to ANSI and works independently of spark.sql.ansi.enabled. The pandas API on Spark also changed: in Spark 4.0 it raised an exception under ANSI mode unless you turned ANSI mode off or set compute.fail_on_ansi_mode to False, and since Spark 4.1 it works under ANSI mode when compute.ansi_mode_support is True, the default.

How Databricks approaches ANSI mode

Databricks uses the same default. Its ANSI compliance page states that ANSI mode is "Enabled by default in Apache Spark 4.0 and Databricks Runtime 17.0 and above." Databricks SQL has its own ANSI_MODE parameter: for accounts created on or after October 19, 2022, the system default is TRUE, and you can change it per session with SET or globally through SQL configuration parameters or the SQL Warehouse API. Serverless notebooks and jobs let you set only a short list of Spark properties, and spark.sql.ansi.enabled is one of them, with a default of true. The Databricks docs also recommend explicit cast or try_cast over relying on ANSI_MODE = FALSE.

FAQs

Does ANSI mode make Spark SQL fully ANSI-standard?

No. The Spark docs say some ANSI dialect features "may be not from the ANSI SQL standard directly, but their behaviors align with ANSI SQL's style." Reserved-keyword enforcement remains a separate opt-in through spark.sql.ansi.enforceReservedKeywords.

Which version made ANSI mode the default?

Apache Spark 4.0.0, released May 23, 2025. The SQL migration guide entry reads: "Since Spark 4.0, spark.sql.ansi.enabled is on by default."

Will my Spark 3.5 SQL break on Spark 4.x?

Only where it hits invalid input or relies on a cast ANSI mode disallows. Those spots now fail with named error classes, so run representative data through Spark 4.x before you cut over and fix each failure with a try_* function or a data correction.

What's the narrowest way to opt out?

Rewrite only the risky expressions with try_* functions. If a statement truly needs the legacy dialect, run SET spark.sql.ansi.enabled = false in that session before it and set it back to true afterwards.