How lazy is the Snowflake SQL compiler and execution?

Question

Can I rely on Snowflake's lazy evaluation for testing and exception throwing?

Are CTEs lazily evaluated?

Is this documented?

(related question: Assertions in Snowflake)

Gertrudis

You're mis construing the processes involved. SQL isn't executed, it's essentially compiled as you say, in to an execution plan (it's not quite the same as compiling; SQL is not imperative, it's a declarative language, you declare the problem, the DBMS is free to solve it how it likes). Only when that plan is executed do these errors materialise. If you write a query with code branches that can never be executed, those branches don't make it in to the plan. If the plan has no divide by zeros in it, regardless of whether the SQL statement did, no divide by zero error will ever get thrown. – Prophesy

Thanks @Prophesy for the feedback. I put this out there especially to get other's thoughts on these findings. – Gertrudis

Btw @Prophesy - when I run an EXPLAIN for the extreme laziness example, the plan does show an UNION_ALL(1, 1 / 0) – Gertrudis

Answer 1

I wanted to share my own experiments on this regard. Lazy evaluation is very useful for optimization purposes, and also for testing. I couldn't find documentation promising this behavior, so these experiments help determine current behavior

Throwing exceptions for tests

These potential division by 0 exceptions are not thrown:

select coalesce(1, 1/0), iff(true, 1, 1/0);

This is very useful when writing a sql script to verify results. For example, to verify if a query brings back 3 rows or throw an exception:

select iff(count(*)=3, 1, 1/0)
from (
    select * from values(1),(2),(3)
);

CTE optimization

Let's find out if Snowflake optimizes CTEs, or if they get evaluated no matter what:

with oh_no as (
  select 1/0 oh_no
), fine as (
  select 1 fine
)

select *
from fine
;

The results are good: That CTE doesn't throw an exception, as it's not evaluated as it's not needed.

Extreme laziness

This is an interesting one:

with oh_no as (
  select 1/0 oh_no
), fine as (
  select 1 fine
)

select *
from (
    select * from fine
    union all 
    select * from oh_no
)
limit 1
;

In theory an exception should be thrown out of the union. But Snowflake sees that the LIMIT 1 has been evaluated, and it doesn't waste time processing further rows.

This means the same query might throw errors depending on the order in which rows are processed, and the errors won't be thrown if processing an initial number of rows satisfies the query requirements.

But all these examples are constants?

As @MatBailie notes, all of these examples are constant so they could be optimized before being executed.

Let me add this example for testing the results of a query over an actual table:

select iff(count(*)=7, 1, 1/0)
from (
    select *
    from snowflake_sample_data.tpch_sf001.customer
    where c_phone like '18-8%'
);

The exception is not thrown, as the table has exactly 7 records that match the condition. Good.

Same for the extreme laziness example, with actual data:

with oh_no as (
    select 1/0 oh_no
), fine as (
    select c_custkey
    from snowflake_sample_data.tpch_sf001.customer
    where c_phone like '18-8%'
    limit 1
)

select *
from (
    select * from fine
    union all 
    select * from oh_no
)
limit 1
;

Gertrudis

These examples are also purely based on literal values; there are no unknowns at 'compile time'. Essentially, the last query can be algebraically reduced to SELECT 1 fine, before even finalising the plan. Were you to have fine AS (SELECT 1 fine FROM some_table) there would be an unknown number of rows in fine. As fine could therefore be empty, the drastic simplification would not be valid. This would allow the oh_no to make it's way in to the plan, and yield different behaviour. – Prophesy

Thanks @MatBailie! I added an example testing with actual data. – Gertrudis

Obviously a bit past the initial question but I'm curious as to if you could use a snowflake-esque tryCatch() wrapper like: select tryCatch(iff(1=2, true, 1/0), 'ERROR: 1 does not equal 2') to stop the query and elegantly handle the error instead of [22012][100051] Division by zero error – Thicket

@ReidWilliams I like this assert() UDF solution for that https://mcmap.net/q/1663692/-assertions-in-snowflake – Gertrudis

I am rather sure some things are defined by the ANSI SQL spec like COALESCE not evaluation order. Which could be classified as lazy, where-as snowflake is anti-lazy (agressive) in it's JSON handling and will execute a TO_NUMBER before the where clauses (at times/conditions) thus violating ANSI SQL, because it's faster, and you "can" flip to the TRY_TO_NUMBER to avoid the exceptions. – Candis

@SimeonPilgrim - that's interesting! Do you have some examples to share? (in an additional answer?) – Gertrudis

Answer 2

I had some problems that looks like to be related to lazy evaluation this week, I'll share here with you. Basically using CTEs and filter them progressively doesn't work properly. I had a query that looked something like the following:

WITH cte_1 AS (
    SELECT field1, field2, field3
    FROM table1
    WHERE field1 = 'A'
    GROUP BY ALL
)
, cte_2 AS (
    SELECT field1, field2, field3
    FROM table1
    WHERE field1 = 'B'
    GROUP BY ALL
)
, cte_3 AS (
    SELECT * FROM cte_1
    UNION ALL
    SELECT * FROM cte_2
)
SELECT *
FROM cte_3
WHERE field2 IS NOT NULL

The results behaved like field2 IS NOT NULL was applied first of all and then it considered the other filters. The problem is that both filters in the cte_1 and cte_2 would determine the records with NULL values in the field2.

I solved the problem creating a temporary table with cte_3 results:

CREATE OR REPLACE TEMPORARY TABLE cte_3 AS
WITH cte_1 AS (
    SELECT field1, field2, field3
    FROM table1
    WHERE field1 = 'A'
    GROUP BY ALL
)
, cte_2 AS (
    SELECT field1, field2, field3
    FROM table1
    WHERE field1 = 'B'
    GROUP BY ALL
)
SELECT * FROM cte_1
UNION ALL
SELECT * FROM cte_2
;

SELECT *
FROM cte_3
WHERE field2 IS NOT NULL

Finisterre