Snowflake IDENTIFIER() not always working

Question

In a Snowflake stored procedure, I'm executing dynamic SQL. I want to pass the table names into the queries using bound variables. Because they are variables, I need to wrap them with IDENTIFIER() (https://docs.snowflake.com/en/sql-reference/identifier-literal.html).

However, this seems to work in some cases and not others.

This works (using Javascript string interpolation to put the target table into the query, but using bound variables for the TMP table) :

    var deleteResults = snowflake.execute({sqlText:`
    DELETE FROM ${threePartTargetTable} TARGET
    USING IDENTIFIER(?) TMP
    WHERE TARGET.DOCUMENT_DESCRIPTOR_ID = TMP.DOCUMENT_DESCRIPTOR_ID
    AND TARGET.DOCUMENT_DESCRIPTOR_ID NOT IN (SELECT DOCUMENT_DESCRIPTOR_ID FROM IDENTIFIER(?) WHERE END_ON = '12/31/9999 12:00:00 AM');`
  ,binds:[
    tempTableName,
    tempTableName
  ]});

But this (using bound variables for all of the table names) returns

invalid identifier 'TARGET.DOCUMENT_DESCRIPTOR_ID'

    var deleteResults = snowflake.execute({sqlText:`
    DELETE FROM IDENTIFIER(?) TARGET
    USING IDENTIFIER(?) TMP
    WHERE TARGET.DOCUMENT_DESCRIPTOR_ID = TMP.DOCUMENT_DESCRIPTOR_ID
    AND TARGET.DOCUMENT_DESCRIPTOR_ID NOT IN (SELECT DOCUMENT_DESCRIPTOR_ID FROM IDENTIFIER(?) WHERE END_ON = '12/31/9999 12:00:00 AM');`
  ,binds:[
    threePartTargetTable,
    tempTableName,
    tempTableName
  ]});

This is another statement taken from the same procedure, which uses the same bound variable for the target table, and this works fine :

    var insertResults = snowflake.execute({sqlText:`INSERT INTO IDENTIFIER(?)
                        (
                            DOCUMENT_DESCRIPTOR_ID
                          , NAME
                        )
                        SELECT  TMP.DOCUMENT_DESCRIPTOR_ID
                              , TMP.NAME
                        FROM  ${tempTableName} TMP
                        WHERE TMP.END_ON = '12/31/9999 12:00:00 AM'
                        AND   TMP.DOCUMENT_DESCRIPTOR_ID NOT IN (SELECT DOCUMENT_DESCRIPTOR_ID FROM IDENTIFIER(?));`
  ,binds:[
    threePartTargetTable,
    threePartTargetTable
  ]});

The documentation doesn't mention any limitations of where IDENTIFIER() can be used. Does anyone know why the second piece of code doesn't work?

user_5070879 · Answer

It seems the issue is the alias which for bound variable cannot be properly resolved. Similar behavior could be observed using Snowflake scripting.

Demo:

CREATE OR REPLACE TABLE test1(ID)  AS SELECT 1 UNION ALL SELECT 2;
CREATE OR REPLACE TABLE test2(ID)  AS SELECT 2;

Using session variables(works):

SET a = 'test1';
SET b = 'test2';

DELETE FROM IDENTIFIER($a) AS TARGET
USING IDENTIFIER($b) AS TMP
WHERE TARGET.ID = TMP.ID;

Using Java Script procedure:

create or replace procedure sp_delete(a STRING, b STRING)
returns float 
language javascript
as
$$
var deleteResults = snowflake.execute({sqlText:`
DELETE FROM IDENTIFIER(?) TARGET
USING IDENTIFIER(?) TMP
WHERE TARGET.ID = TMP.ID;`
  ,binds:[A, B]});
$$ ;

CALL sp_delete('test1', 'test2');

SQL compilation error: invalid identifier 'TARGET.ID' At Snowflake.execute

Using Snowflake Scripting:

create or replace procedure sp_delete(a STRING, b STRING)
returns float 
language SQL
as
$$
BEGIN
    DELETE FROM IDENTIFIER(:A) AS TARGET
    USING IDENTIFIER(:B) AS TMP
    WHERE TARGET.ID = TMP.ID;
    
    RETURN 0;
END;
$$;


CALL sp_delete('test1', 'test2');

invalid identifier 'TARGET.ID'

The workaround is to rewrite query to eqivalent form without alias:

create or replace procedure sp_delete(a STRING, b STRING)
returns float 
language javascript
as
$$
var deleteResults = snowflake.execute({sqlText:`
    DELETE FROM IDENTIFIER(?)
    WHERE ID IN (SELECT TMP.ID FROM IDENTIFIER(?) AS TMP) ;`
  ,binds:[A, B]});
$$;

For actual query:

DELETE FROM IDENTIFIER(?) TARGET
USING IDENTIFIER(?) TMP
WHERE TARGET.DOCUMENT_DESCRIPTOR_ID = TMP.DOCUMENT_DESCRIPTOR_ID
  AND TARGET.DOCUMENT_DESCRIPTOR_ID NOT IN (SELECT DOCUMENT_DESCRIPTOR_ID 
                                            FROM IDENTIFIER(?) 
                                            WHERE END_ON = '12/31/9999 12:00:00 AM');

=>

DELETE FROM IDENTIFIER(?) 
WHERE DOCUMENT_DESCRIPTOR_ID IN (SELECT DOCUMENT_DESCRIPTOR_ID FROM IDENTIFIER(?))
  AND DOCUMENT_DESCRIPTOR_ID NOT IN (SELECT DOCUMENT_DESCRIPTOR_ID 
                                     FROM IDENTIFIER(?) 
                                     WHERE END_ON = '12/31/9999 12:00:00 AM');

Snowflake IDENTIFIER() not always working

1 Answers