Troubleshooting SQL UDFs

This topic provides troubleshooting information about SQL UDFs (user-defined functions).

In this Topic:

Troubleshooting

Tips

If using a SQL UDF in a masking policy, ensure the data type of the column, UDF, and masking policy match.

Error Message: Unsupported subquery type

Cause

If a UDF contains a query expression, then the UDF can act as a subquery. If a subquery is passed a column name, then the subquery can act as a correlated subquery. If a correlated subquery violates the Snowflake rules for correlated subqueries, then the user gets the error message Unsupported subquery type. The example below shows an invalid correlated subquery, and how a UDF can act like a similar invalid correlated subquery.

Create a pair of tables and load data into them:

CREATE TABLE stores (store_ID INTEGER, city VARCHAR);
CREATE TABLE employees (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 employees (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')
    ;

The following SQL statement contains a correlated subquery that does not follow Snowflake rules. This code causes an Unsupported subquery type error:

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

The code below creates and then calls a subquery-like UDF in a way that creates a correlated subquery similar to the one shown above:

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(employees.store_ID)
    FROM employees;
Solution #1

If the UDF contains a query expression, then call the UDF only in ways consistent with the rules for subqueries.

For example, the following statement calls the UDF with a constant rather than with a column name, so the UDF does not act like a correlated subquery:

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+
Solution #2

In some cases, you can re-write the UDF to achieve the same goal a different way. A correlated subquery is allowed if the subquery can be statically determined to return one row. The following UDF uses an aggregate function and therefore returns only one row:

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, subquery_like_udf_2(employees.store_ID)
    FROM employees;
+-------------+-----------------------------------------+
| EMPLOYEE_ID | SUBQUERY_LIKE_UDF_2(EMPLOYEES.STORE_ID) |
|-------------+-----------------------------------------|
|        1001 | Winnipeg                                |
|        1002 | Winnipeg                                |
|        2001 | Toronto                                 |
|        2002 | Toronto                                 |
|        2002 | Toronto                                 |
+-------------+-----------------------------------------+