Why does Snowflake LAST_QUERY_ID returns NULL?

Question

Given the following procedure

CREATE OR REPLACE PROCEDURE z()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
$$

const what = snowflake.execute( {sqlText: "select $1 as value from values (1), (2), (3);"} )

return 'Hi There';
$$
;

If I run the 2 statements below

CALL z();

select * from table(result_scan(-2));

I get an error "SQL Error [709] [02000]: Statement 01912a0c-01c1-0574-0000-4de50036137e not found"

If I run the 2 statements below

CALL z();

SELECT LAST_QUERY_ID(), LAST_QUERY_ID(-1), LAST_QUERY_ID(-2);

It shows me that LAST_QUERY_ID() and LAST_QUERY_ID(-1) are identical but also that LAST_QUERY_ID(-2) returns NULL...

Any idea why it returns NULL rather than something that would allow me to retrieve the result of my query "select $1 as value from values (1), (2), (3);"?

Thanks

Gokhan Atil Gokhan Atil · Accepted Answer · 2019-12-27T12:32:17

It shows me that LAST_QUERY_ID() and LAST_QUERY_ID(-1) are identical but also that LAST_QUERY_ID(-2) returns NULL

It's expected to see the same query ID for LAST_QUERY_ID() and LAST_QUERY_ID(-1), because they are same (the default value for LAST_QUERY_ID is "-1").

On the other hand, the LAST_QUERY_ID(-2) should not returns NULL, and the "select * from table(result_scan(-2))" query should not fail.

If you define the function with CALLER rights, you can see that the LAST_QUERY_ID(-2) returns the query ID and the "select * from table(result_scan(-2))" query works:

CREATE OR REPLACE PROCEDURE z()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$

const what = snowflake.execute( {sqlText: "select $1 as value from values (1), (2), (3);"} )

return 'Hi There';
$$
;

As a workaround, you can use the history window to check the latest queries. You should be able to see your query (01912a0c-01c1-0574-0000-4de50036137e) in the history tab. You will notice that these queries are executed by same user, and in the same session. Therefore, there shouldn't be a restriction on listing these queries.

I am able to reproduce the issue on my test environment, and I will report the issue to the development team. If you have access to Snowflake support, it could be better to submit a support case regarding to this issue, so you can easily follow the process.

Why does Snowflake LAST_QUERY_ID returns NULL?

2 Answers