Join on recursive CTE in snowflake disobeys where clause

user_17378004 0
Viewed 173

Consider the following views and CTE:

CREATE OR REPLACE VIEW SubSet (Id)
AS (
          SELECT '123'
UNION ALL SELECT '456'
UNION ALL SELECT 'x'
)

CREATE OR REPLACE VIEW MainSet (Id)
AS (
          SELECT 123
UNION ALL SELECT 456
UNION ALL SELECT 789
)


WITH myCte (id, cnt) AS
(
  SELECT id, 1 AS cnt FROM SubSet
  UNION ALL
  SELECT id
  ,cnt + 1 FROM myCte WHERE cnt < 4 AND id <> 'x'
)
SELECT *
FROM MainSet ms
JOIN (
  SELECT id FROM myCte WHERE id <> 'x'
) X ON ms.id = X.id

Notice that 'x' is filtered out, both on the recursive part of the CTE and on the Sub Query from the CTE.

This worked yesterday. But as of today, it fails with "Numeric value 'x' is not recognized".

If I remove the recursive part, it won't fail:

WITH myCte (id, cnt) AS
(
  SELECT id, 1 AS cnt FROM SubSet
  --UNION ALL
  --SELECT id
  --,cnt + 1 FROM myCte WHERE cnt < 4 AND id <> 'x'
)
SELECT *
FROM MainSet ms
JOIN (
  SELECT id FROM myCte WHERE id <> 'x'
) mc ON ms.id = mc.id

How can the query match on 'x' when it is filtered out? It only seems to apply when using recursive CTEs.

snowflake-cloud-data-platform common-table-expression
1 Answers

Ultimately I believe the issue stems from the ON clause in the final SELECT query. Snowflake is attempting an implicit cast to a number on X.id to make the join happen and it's failing.

I would imagine there is a query optimization step taking place that is taking the ON predicate and pushing it sooner in the execution path than the predicate in the WHERE clause. To me that feels like a bug in Snowflake, but a bug that is being exacerbated by slightly sloppy SQL and not terribly clean data where implicit casting is allowed to happen.

You can get around it by changing your ON criteria to:

ON cast(ms.id as string) = X.id

This ensures that Snowflake doesn't have to decide to either cast your ms.id as string or cast your X.id as numeric where it may make the wrong choice.

Working example (note removing the cast in this example will cause the error to pop once again):

WITH SubSet (Id)
AS (
    SELECT '123'
    UNION ALL SELECT '456'
    UNION ALL SELECT 'x'
)
,MainSet (Id)
AS (
    SELECT 123
    UNION ALL SELECT 456
    UNION ALL SELECT 789
)
,myCte (id, cnt) AS
(
  SELECT id, 1 AS cnt FROM SubSet
  UNION ALL
  SELECT id
      ,cnt + 1 
  FROM myCte 
  WHERE cnt < 4 AND id <> 'x'
)   
SELECT *
FROM MainSet ms
  JOIN (
    SELECT id FROM myCte WHERE id <> 'x'
  ) X ON cast(ms.id as string) = X.id;
user_22210010
Related