SQL UDFs 故障排除

本主题提供有关 SQL UDFs（用户定义函数）的故障排除信息。

故障排除

技巧

如果在 掩码策略 中使用 SQL UDF,请确保列的数据类型 UDF 与掩码策略匹配。

错误消息：Unsupported subquery type

原因:

如果 UDF 包含查询表达式，则 UDF 可以充当 子查询。如果向子查询传递了列名，则该子查询可以充当 相关子查询。如果相关子查询违反了 Snowflake 的相关子查询规则，用户将收到错误消息 Unsupported subquery type。以下示例展示了一个无效的相关子查询，以及 UDF 如何像类似的无效相关子查询一样工作。

创建一对表并将数据加载到其中：

CREATE TABLE stores (store_ID INTEGER, city VARCHAR);
CREATE TABLE employee_sales (employee_ID INTEGER, store_ID INTEGER, sales NUMERIC(10,2), 
    sales_date DATE);
INSERT INTO stores (store_ID, city) VALUES 
    (1, 'Winnipeg'),
    (2, 'Toronto');
INSERT INTO employee_sales (employee_ID, store_ID, sales, sales_date) VALUES 
    (1001, 1, 9000.00, '2020-01-27'),
    (1002, 1, 2000.00, '2020-01-27'),
    (2001, 2, 6000.00, '2020-01-27'),
    (2002, 2, 4000.00, '2020-01-27'),
    (2002, 2, 5000.00, '2020-01-28')
    ;

以下 SQL 语句包含未遵守 Snowflake 规则的相关子查询。此代码会导致 Unsupported subquery type 错误：

SELECT employee_ID,
       store_ID,
       (SELECT city FROM stores WHERE stores.store_ID = employee_sales.store_ID)
    FROM employee_sales;

以下代码创建并调用类似子查询的 UDF，这会创建类似于上面所示的相关子查询：

CREATE FUNCTION subquery_like_udf(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT city FROM stores WHERE stores.store_ID = X
    $$;

SELECT employee_ID, subquery_like_udf(employee_sales.store_ID)
    FROM employee_sales;
解决方案 #1:

如果 UDF 包含查询表达式，则仅以遵守 子查询 规则的方式来调用 UDF。

例如，以下语句使用常量而不是列名称来调用 UDF，因此，UDF 不会像相关子查询那样工作：

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+
解决方案 #2:

在某些情况下，可以重写 UDF，以通过另一种方式实现同一个目标。如果可以静态地确定相关子查询返回一行，则允许运行该子查询。以下 UDF 使用聚合函数，因此仅返回一行：

CREATE FUNCTION subquery_like_udf_2(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT ANY_VALUE(city) FROM stores WHERE stores.store_ID = X
    $$;

SELECT employee_ID, sales_date, subquery_like_udf_2(employee_sales.store_ID)
    FROM employee_sales
    ORDER BY employee_ID, sales_date;
+-------------+------------+----------------------------------------------+
| EMPLOYEE_ID | SALES_DATE | SUBQUERY_LIKE_UDF_2(EMPLOYEE_SALES.STORE_ID) |
|-------------+------------+----------------------------------------------|
|        1001 | 2020-01-27 | Winnipeg                                     |
|        1002 | 2020-01-27 | Winnipeg                                     |
|        2001 | 2020-01-27 | Toronto                                      |
|        2002 | 2020-01-27 | Toronto                                      |
|        2002 | 2020-01-28 | Toronto                                      |
+-------------+------------+----------------------------------------------+