Snowflake调用含子查询的UDF报'Unsupported subquery type'错误

问题现象 #

在SELECT语句的WHERE子句中使用自定义UDF，或直接使用关联子查询时，触发错误：

Unsupported subquery type cannot be evaluated

相关代码如下：

含UDF的查询 #

SELECT * FROM FINAL_MATCH_TABLE as FMT
WHERE MY_UDF(FMT.col1, FMT.col2) = TRUE;

原始UDF定义 #

CREATE OR REPLACE FUNCTION MY_UDF(col1_in INTEGER, col2_in INTEGER)
RETURNS BOOLEAN
AS
$$
    SELECT my_boolean FROM FIRST_MATCH
        WHERE col1 = col1_in
        AND col2 = col2_in
$$
;

替换为关联子查询的查询 #

SELECT * FROM FINAL_MATCH_TABLE as FMT
WHERE (SELECT my_boolean FROM FIRST_MATCH as FM
        WHERE FM.col1 = FMT.col1
        AND FM.col2 = FMT.col2)= TRUE;

问题原因 #

原始UDF内的查询（或直接的关联子查询）可能返回多行结果，而WHERE子句要求条件判断必须返回单个确定值，Snowflake无法处理这种返回多行的子查询/UDF，因此抛出错误。

解决方案 #

使用聚合函数BOOLAND_AGG()将多行布尔值合并为单个布尔结果，修改后的UDF定义如下：

CREATE OR REPLACE FUNCTION MY_UDF(col1_in INTEGER, col2_in INTEGER)
RETURNS BOOLEAN
AS
$$
    SELECT BOOLAND_AGG(my_boolean) FROM FIRST_MATCH
        WHERE col1 = col1_in
        AND col2 = col2_in
$$
;

函数说明 #

• BOOLAND_AGG()会对匹配行的my_boolean进行逻辑与聚合：
  • 所有匹配行的my_boolean为TRUE时，返回TRUE
  • 存在任意匹配行的my_boolean为FALSE时，返回FALSE
  • 无匹配行时返回NULL，若需要默认值可添加COALESCE，例如：SELECT COALESCE(BOOLAND_AGG(my_boolean), FALSE) FROM ...

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