Dépannage d’UDFs SQL

Cette rubrique fournit des informations de dépannage sur les UDFs SQL (fonctions définies par l’utilisateur).

Dans ce chapitre :

Dépannage

Message d’erreur : Unsupported subquery type

Cause

Si un UDF contient une expression de requête, l’UDF peut agir comme une sous-requête. Si une sous-requête reçoit un nom de colonne, la sous-requête peut agir comme une sous-requête corrélée. Si une sous-requête corrélée enfreint les règles Snowflake pour les sous-requêtes corrélées, l’utilisateur reçoit le message d’erreur Unsupported subquery type. L’exemple ci-dessous montre une sous-requête corrélée non valide et comment un UDF peut agir comme une sous-requête corrélée non valide similaire.

Créer une paire de tables et y charger des données :

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

L’instruction SQL suivante contient une sous-requête corrélée qui ne suit pas les règles Snowflake. Ce code provoque une erreur Unsupported subquery type :

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

Le code ci-dessous crée puis appelle un UDF de sous-requête de manière à créer une sous-requête corrélée similaire à celle illustrée ci-dessus :

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 numéro 1

Si l’UDF contient une expression de requête, appelez l’UDF uniquement de manière cohérente avec les règles des sous-requêtes.

Par exemple, l’instruction suivante appelle l’UDF avec une constante plutôt qu’avec un nom de colonne, donc l’UDF n’agit pas comme une sous-requête corrélée :

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+
Solution numéro 2

Dans certains cas, vous pouvez réécrire l’UDF pour atteindre le même objectif d’une manière différente. Une sous-requête corrélée est autorisée si la sous-requête peut être déterminée statiquement pour renvoyer une ligne. L’UDF suivant utilise une fonction d’agrégation et ne renvoie donc qu’une seule ligne :

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