Solução de problemas em UDFs de SQL¶

Dicas¶

Se você usar uma UDF de SQL em uma política de mascaramento, certifique-se de que o tipo de dados da coluna, a UDF e a política de mascaramento sejam correspondentes.

Mensagem de erro: Unsupported subquery type¶

Causa:

Se uma UDF contém uma expressão de consulta, a UDF pode atuar como uma subconsulta. Se uma subconsulta recebe um nome de coluna, a subconsulta pode atuar como uma subconsulta correlacionada. Se uma subconsulta correlacionada viola as regras do Snowflake para subconsultas correlacionadas, o usuário recebe a mensagem de erro Unsupported subquery type. O exemplo abaixo mostra uma subconsulta correlacionada inválida, e como uma UDF pode agir como uma subconsulta correlacionada inválida semelhante.

Crie um par de tabelas e carregue dados nelas:

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')
    ;

Copy

A seguinte instrução SQL contém uma subconsulta correlacionada que não segue as regras do Snowflake. Esse código causa um erro Unsupported subquery type:

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

Copy

O código abaixo cria e depois chama uma UDF que age como subconsulta de forma a criar uma subconsulta correlacionada semelhante à mostrada acima:

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

Copy

SELECT employee_ID, subquery_like_udf(employee_sales.store_ID)
    FROM employee_sales;

Copy

Solução nº 1:

Se a UDF contém uma expressão de consulta, somente chame a UDF de forma consistente com as regras para subconsultas.

Por exemplo, a instrução a seguir chama a UDF com uma constante e não com um nome de coluna. Portanto, a UDF não age como uma subconsulta correlacionada:

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+

Copy

Solução nº 2:

Em alguns casos, você pode reescrever a UDF para alcançar o mesmo objetivo de maneira diferente. Uma subconsulta correlacionada é permitida se é possível determinar estaticamente que a subconsulta retornará uma linha. A UDF a seguir usa uma função agregada e, portanto, retorna apenas uma linha:

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

Copy

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                                      |
+-------------+------------+----------------------------------------------+

Copy