Problem

When querying a view, you notice unexpected behavior related to ANSI mode. Particularly, when you change the spark.sql.ansi.enabled configuration inline, the change does not reflect in the query execution. 

Example

In the following code, you create a table and then a view with ANSI mode enabled initially. You select c1, c1 / 0 as c2, which is not ANSI compliant. When you try to SELECT * FROM the view, it fails as expected with a DIVIDE_BY_ZERO error. 

You then disable ANSI mode inline, and run SELECT * FROM the view again. The query still fails, but you expect it to succeed.

%sql
-- create a table
CREATE OR REPLACE TABLE <example-table> (c1 int);
INSERT INTO <example-table> VALUES (1);

-- create a view with ANSI mode enabled
SET spark.sql.ansi.enabled=true;
CREATE OR REPLACE VIEW <example-view> AS
SELECT
  c1,
  c1 / 0 as c2 -- ℹ️ this does *not* comply with ANSI standards so querying this view with ANSI enabled should throw an exception. 
FROM <example-table>;

--
-- test #1
--
-- expectation: this should fail given that ANSI mode is *enabled*
SELECT * FROM <example-view>;
-- result: ❌ throws a `DIVIDE_BY_ZERO` error as expected

--
-- test #2
--
SET spark.sql.ansi.enabled=false;
-- expectation: this should succeed given that ANSI mode is *disabled*
SELECT * FROM <example-view>;
-- result: ❌ throws a `DIVIDE_BY_ZERO` error. this is unexpected

Cause

The ANSI session level configuration set at the time of view creation persists with the view definition.

Solution

Recreate the view with the desired ANSI mode. 

If you prefer not to recreate the view, you can force ANSI exceptions inline using functions such as try_cast, try_add, or try_divide in the non-ANSI operations. For more information, refer to the try_cast function, try_add function, and try_divide function documentation.