Snowflake中CTE先关联再过滤？出现数据类型错误

问题场景 #

执行如下Snowflake查询：

With CTE as(
select account_id -- 未校验的字段，包含文本内容
from mytable
where try_to_numeric(account_id) is not null
)
select *
from
accounts
inner join CTE on accounts.account_id = CTE.account_id

执行后报错：“来自CTE的'my dog is cool'不是数字！”

疑问：JOIN操作是否在try_to_numeric过滤之前执行？能否通过查询计划对此进行确认？

解答 #

1. JOIN操作可能被优化器提前执行
   Snowflake的查询优化器会基于数据分布、统计信息等对查询逻辑进行重写，有可能将JOIN操作提前到CTE的过滤逻辑之前执行——优化器认为这样能减少后续处理的数据量提升性能，但在这个场景下，由于accounts.account_id是数字类型，CTE中未被过滤干净的文本类型account_id在JOIN时会触发隐式转换，最终导致转换失败报错。

2. 通过查询计划确认执行顺序
   可以通过两种方式验证：

• Web UI查看Query Profile：在Snowflake Web UI执行查询后，点击结果上方的**“Query Profile”**标签，在树形计划中找到JOIN节点和对应try_to_numeric过滤的Filter节点，查看两者的位置关系：若JOIN节点在Filter节点之前，说明优化器提前执行了JOIN；也可通过节点的父子关系确认数据流向顺序。
• 执行EXPLAIN命令：运行以下命令生成文本格式的查询计划：

EXPLAIN
With CTE as(
select account_id
from mytable
where try_to_numeric(account_id) is not null
)
select *
from
accounts
inner join CTE on accounts.account_id = CTE.account_id

在输出结果中查找JOIN和FILTER操作的先后顺序，即可明确优化器实际的执行逻辑。

3. 报错解决方法
   为避免隐式转换报错，可在CTE中明确将account_id转换为数字类型：

With CTE as(
select try_to_numeric(account_id) as account_id -- 明确转换为数字类型
from mytable
where try_to_numeric(account_id) is not null
)
select *
from
accounts
inner join CTE on accounts.account_id = CTE.account_id

这样即便优化器调整执行顺序，CTE输出的account_id已是数字类型，不会在JOIN阶段触发转换错误。

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