Dépannage d’UDFs SQL

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

Résolution des problèmes

Conseils

Si vous utilisez une UDF SQL dans une politique de masquage, assurez-vous que le type de données de la colonne, l’UDF, et la politique de masquage correspondent.

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

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 = employee_sales.store_ID)
    FROM employee_sales;

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(employee_sales.store_ID)
    FROM employee_sales;
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, 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                                      |
+-------------+------------+----------------------------------------------+