CODEWITHSUNDEEP
snowflake-cloud-data-platform
Eric Mamet
Eric Mamet

Reputation: 3641

Why does Snowflake LAST_QUERY_ID returns NULL?

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

Upvotes: 0

Views: 1229

Answers (2)

Eric Mamet
Eric Mamet

Reputation: 3641

For the record, here is the answer I got from Snowflake Support

This is an expected behavior because of security limitation placed on purpose (queries from inside a SP do not have access to queries outside and vice versa). We put a blanket limitation that the world of inside and outside such SPs should be separated. From one the "result" of a query from another cannot be accessed. Yes, If we declare the procedure with caller rights, everything (LAST_QUERY_ID and RESULT_SCAN) works fine in this case. There is a discussion going on to implement this use-case when the owner and caller of an "owner's right" SP are the same, all this limitations should be lifted. But we cannot be sure about the timeline, I can link this case with that discussion so that we have this record.

Sounds good to me :-)

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10039

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.

Upvotes: 2

Related Questions