Dépannage d’UDFs SQL¶

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

Copy

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;

Copy

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

Copy

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

Copy

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

Copy

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

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