Snowflake递归CTE关联查询时WHERE子句过滤规则失效问题

问题根因 #

这个问题是查询优化器的谓词下推逻辑、递归CTE的执行限制、隐式类型转换三者共同作用的结果：

• 首先两个视图的字段类型不匹配：SubSet.Id是字符串类型（存储了'123'、'456'、'x'三个值），MainSet.Id是数值类型。关联条件ms.id = X.id会触发隐式类型转换，数据库默认会把字符串类型的X.id转成数值，再和数值类型的ms.id比较。
• 你虽然在递归分支和外层查询都加了id <> 'x'的过滤，但是数据库优化器为了提升执行效率，会尝试将关联条件的隐式转换操作提前执行。普通非递归CTE的结构简单，优化器可以正确调整执行顺序：先执行id <> 'x'过滤掉'x'，再做类型转换，所以不会报错。
• 递归CTE的执行逻辑特殊，需要分锚点成员、递归成员多轮迭代计算。优化器无法将外层的过滤/转换操作安全地推到递归迭代流程之前，反而可能在迭代过程中，id <> 'x'过滤还未生效时，就提前触发了'x'到数值的类型转换，直接抛出报错。

修复方案 #

你可以任选以下一种方案解决：

1. 从源头避免'x'进入递归流程，在CTE锚点查询就加过滤：

WITH myCte (id, cnt) AS
(
  SELECT id, 1 AS cnt FROM SubSet WHERE id <> 'x' -- 锚点阶段就过滤掉x
  UNION ALL
  SELECT id, cnt + 1 FROM myCte WHERE cnt < 4
)
-- 后续查询逻辑保持不变

2. 主动显式转换类型，避免隐式转换把字符串转数值：

SELECT *
FROM MainSet ms
JOIN (
  SELECT id FROM myCte WHERE id <> 'x'
) X ON CAST(ms.id AS VARCHAR) = X.id -- 把数值转成字符串匹配，不会触发x的转换错误

3. 用类型安全的转换函数处理字符串字段，避免非法值报错（以支持TRY_CAST的数据库为例）：

SELECT *
FROM MainSet ms
JOIN (
  SELECT TRY_CAST(id AS INT) AS id FROM myCte WHERE id <> 'x'
) X ON ms.id = X.id

内容的提问来源于stack exchange，提问作者Micke_xyz