Snowflake UDF接收多列数组求最大值报错：不支持的子查询类型

问题描述 #

需要创建一个Snowflake UDF，接收来自表不同列的数值数组并返回最大值。当前使用的SQL UDF代码如下：

CREATE OR REPLACE FUNCTION get_max(input_array array)
 RETURNS double precision
AS '
  WITH t AS
  (
     SELECT value::integer as val from table(flatten(input => input_array))
     WHERE VAL IS NOT NULL
  ),
  cnt AS
  (
    SELECT COUNT(*) AS c FROM t
  )
  SELECT MAX(val)::float
  FROM 
  (
    SELECT val FROM t
  ) t2
'

当传入表列组成的数组（如select get_max(to_array([table.col1, table.col2, table.col3]))）时，会触发错误：

Unsupported subquery type cannot be evaluated

但直接用array_construct(7, 120, 2, 4, 5, 80)这类静态构造的数组替换input_array运行SQL时，能正常返回正确结果。

问题原因 #

原SQL UDF中嵌套了多个CTE和子查询，当UDF输入依赖于表的行级数据时，Snowflake的SQL UDF执行计划无法正确解析这种复杂的子查询结构，导致报错。

解决方案 #

方案1：改用JavaScript UDF #

JavaScript UDF可以直接操作数组，逻辑更简洁，且不受SQL子查询的限制：

CREATE OR REPLACE FUNCTION get_max(input_array array)
RETURNS double precision
LANGUAGE JAVASCRIPT
AS '
  const validValues = INPUT_ARRAY.filter(val => val !== null);
  return validValues.length > 0 ? Math.max(...validValues) : null;
';

使用示例：

SELECT get_max(TO_ARRAY([col1, col2, col3])) FROM your_table;

方案2：简化SQL UDF写法 #

如果偏好使用SQL UDF，可以去掉多余的CTE和子查询，直接在flatten后聚合：

CREATE OR REPLACE FUNCTION get_max(input_array array)
RETURNS double precision
AS '
  SELECT MAX(VALUE::integer)::float
  FROM TABLE(FLATTEN(input => input_array))
  WHERE VALUE IS NOT NULL
';

这个简化后的写法能避免原UDF的执行计划问题，同时实现相同的功能——过滤null值后返回数组最大值。

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