Solução de problemas em UDFs de SQL¶

Este tópico fornece informações de solução de problemas sobre UDFs (funções definidas pelo usuário) de SQL.

Solução de problemas¶

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

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;

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
    $$;

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

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

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
    $$;

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

Solução de problemas em UDFs de SQL¶