Snowflake关联子查询时出现不存在值的数值转换错误问题

根本原因 #

这是Snowflake查询优化器的谓词下推优化逻辑导致的执行顺序异常：

• 当你将带类型转换的子查询直接写在JOIN逻辑中时，优化器可能会调整执行顺序，优先执行to_number(external_id,38,0)类型转换，再执行WHERE子句中的过滤条件。此时源表中未被过滤的非法非数值字符串（比如报错中的6162211ca2d88b37ac48dc0d）会触发转换报错，哪怕你写的过滤逻辑本应先排除这些无效值。
• 预创建临时表时，SQL执行顺序严格按照你写的逻辑：先执行WHERE过滤排除所有无效external_id，再对剩下的合法值做类型转换存入临时表，后续关联时不会再触发类型转换，因此不会报错。

可行解决方案 #

• 方案1：使用容错转换函数替代to_number
  把子查询中的to_number替换为try_to_number，该函数转换失败时会返回NULL而非抛出错误，不影响后续关联逻辑。修改后的子查询代码如下：

select
last_day(LA.created_at::date,'month') Month,
LA.rights_holder
from "linear_asset" LA
inner join 
(
    select distinct try_to_number(external_id,38,0) as external_id
    from "Company_assets_ev"
    where provider = 'screenplay'
    and external_id is not null
    and external_id != ''
    and external_id != 'NULL'
) SubQueryJoin on SubQueryJoin.external_id = LA.External_id

• 方案2：强制子查询优先执行过滤逻辑
  在子查询末尾添加OFFSET 0，可以阻止优化器将关联条件下推到子查询内部，保证先执行WHERE过滤再做类型转换，代码示例：

select
last_day(LA.created_at::date,'month') Month,
LA.rights_holder
from "linear_asset" LA
inner join 
(
    select distinct to_number(external_id,38,0) as external_id
    from "Company_assets_ev"
    where provider = 'screenplay'
    and external_id is not null
    and external_id != ''
    and external_id != 'NULL'
    OFFSET 0 -- 阻止谓词下推，强制子查询按编写顺序执行
) SubQueryJoin on SubQueryJoin.external_id = LA.External_id

• 方案3：预生成有效ID集合
  也就是你目前使用的预创建临时表的方式，提前将过滤转换后的合法ID固化后再关联，适合子查询逻辑复杂、需要多次复用的场景。

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