如何在Snowflake中对表所有行执行自定义函数？

现有两个Snowflake自定义函数，单独调用时结果符合预期，但批量应用到表行时报错。

函数定义如下：

CREATE OR REPLACE FUNCTION postgres_generate_series(x_start float, x_end float, stepsize float)
 RETURNS table (series float)
AS
-- Max series size is 10000
$$ 
select (x_start+(-1 + (row_number()) over(order by 0))*stepsize) i
from (select x_start, x_end)
join table(generator(rowcount => 10000)) x
qualify i <= x_end
$$
;

CREATE OR REPLACE FUNCTION myfunction(a float, b float, c float)
 RETURNS float
AS
$$ 
select sum(1/(1+exp(-(series - c)/4)))
from (select 1),
table(postgres_generate_series(a+1,b,1::float))
$$
;

单独执行以下语句均能得到预期结果：

select myfunction(1,10,1);

select myfunction(1,100,1);

但执行批量查询时出现报错：

select
    a,
    b,
    c,
    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
    );

需要改写查询以实现对表所有行执行函数。

问题根源在于标量函数myfunction内部调用表值函数postgres_generate_series时，批量执行时Snowflake的执行引擎无法正确为每一行隔离上下文。以下两种方法可解决问题：

方法一：使用LATERAL JOIN改写查询 #

无需修改现有函数，直接调整批量查询语句，通过横向连接确保每一行独立调用函数：

select
    t.a,
    t.b,
    t.c,
    mf.result
from
    (
        select 1 as a, 10 as b, 1 as c
        union
        select 1 as a, 100 as b, 1 as c
    ) t,
    lateral (select myfunction(t.a, t.b, t.c) as result) mf;

方法二：修改myfunction为表值函数 #

将myfunction改为返回单行列的表值函数，更适配批量场景：

CREATE OR REPLACE FUNCTION myfunction(a float, b float, c float)
 RETURNS table (result float)
AS
$$ 
select sum(1/(1+exp(-(series - c)/4))) as result
from table(postgres_generate_series(a+1,b,1::float))
$$
;

再通过横向连接执行查询：

select
    t.a,
    t.b,
    t.c,
    mf.result
from
    (
        select 1 as a, 10 as b, 1 as c
        union
        select 1 as a, 100 as b, 1 as c
    ) t,
    lateral table(myfunction(t.a, t.b, t.c)) mf;

说明 #

原标量函数批量调用出错，是因为Snowflake处理标量函数内的表值函数时，批量场景下易出现上下文混淆。LATERAL JOIN会为每一行单独初始化函数执行上下文，彻底避免这类问题。

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