snowflake中递归CTE上的Join违反where子句

Question

考虑以下视图和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

请注意，在CTE的递归部分和来自CTE的子查询上，"x"都被过滤掉了。

这昨天奏效了。但截至今天，它以"；无法识别数值"x"；。

如果我删除递归部分，它不会失败：

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

当筛选出"x"时，查询如何匹配？它似乎只适用于使用递归CTE的情况。

Answer 1

最终，我认为问题源于最终SELECT查询中的ON子句。Snowflake正在尝试对X.id上的一个数字进行隐式强制转换以实现联接，但失败了。

我想有一个查询优化步骤正在进行，它接受ON谓词，并在执行路径中比WHERE子句中的谓词更快地推送它。对我来说，这感觉像是Snowflake中的一个bug，但稍微草率的SQL和不太干净的数据会加剧这个bug，因为隐式转换是允许发生的。

您可以通过将ON标准更改为：来绕过它

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

这确保Snowflake不必决定将ms.id强制转换为字符串，也不必将X.id强制转换为数字，因为它可能会做出错误的选择。

工作示例(注意，在本示例中删除强制转换将导致错误再次弹出(：

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;