Snowflake自定义函数传多行含Null/Decimal参数报错的原因与解决

自定义函数代码 #

CREATE OR REPLACE FUNCTION myfunction(a float, b float, c float)
RETURNS float AS
$$ 
select sum(1/(1+exp(-(series - c)/4)))
from (
    select (a + ((row_number()) over(order by 0))*1) series
    from table(generator(rowcount => 10000)) x
    qualify series <= b
)
$$;

调用场景对比 #

单行调用正常 #

以下单行参数调用均能返回预期结果：

select myfunction(1, 10, 1);
select myfunction(1, 100, 1);
select myfunction(1, 10, 1.1);
select myfunction(0, 1, 89.87);
select myfunction(0, 1, null);

含Null/Decimal的多行调用报错 #

执行以下多行参数查询时触发错误：

select myfunction(a, b, c)
from (
    select 1 as a, 10 as b, 1.1 as c
    union
    select 0 as a, 1 as b, null as c
);

错误信息：

"Unsupported subquery type cannot be evaluated"

不含Null/Decimal的多行调用正常 #

仅传入整数参数的多行查询可正常运行：

select a, b, myfunction(a, b, c)
from (
    select 1 as a, 10 as b, 1 as c
    union
    select 1 as a, 100 as b, 1 as c 
);

问题原因 #

Snowflake对SQL UDF处理单行和多行输入时的优化逻辑不同：

• 单行调用时，优化器将函数作为标量计算直接处理，不需要复杂的向量化适配，因此能兼容Null和Decimal类型。
• 多行调用时，优化器会尝试进行向量化优化，但原函数内部嵌套子查询+QUALIFY+ROW_NUMBER()窗口函数的组合，再结合Null/Decimal参数，导致优化器无法解析该子查询的执行逻辑，最终抛出错误。

改写方案 #

方案一：改用表函数+横向连接（LATERAL JOIN） #

将标量函数改为表函数，通过横向连接实现多行参数调用：

CREATE OR REPLACE FUNCTION my_table_function(a float, b float, c float)
RETURNS TABLE(result float) AS
$$ 
select sum(1/(1+exp(-(series - c)/4))) as result
from (
    select (a + (row_number() over(order by 0))*1) series
    from table(generator(rowcount => 10000)) x
    qualify series <= b
)
$$;

调用方式：

select t.a, t.b, t.c, f.result
from (
    select 1 as a, 10 as b, 1.1 as c
    union
    select 0 as a, 1 as b, null as c
) t,
lateral table(my_table_function(t.a, t.b, t.c)) f;

方案二：简化函数内部逻辑 #

用seq4()替代窗口函数生成序列，去掉嵌套子查询和QUALIFY，让优化器更容易解析：

CREATE OR REPLACE FUNCTION myfunction(a float, b float, c float)
RETURNS float AS
$$ 
select sum(1/(1+exp(-(a + seq4() - c)/4)))
from table(generator(rowcount => 10000)) x
where (a + seq4()) <= b;

验证调用（可正常处理含Null/Decimal的多行参数）：

select myfunction(a, b, c)
from (
    select 1 as a, 10 as b, 1.1 as c
    union
    select 0 as a, 1 as b, null as c
);

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