SNOW-1454377: Snowflake Native Apps Stored Procedure fails to execute or return table when called from Task

[Synohara]

1. What version of Python are you using?

   Python 3.11

2. What operating system and processor architecture are you using?

   The environment of the Stored Procedure implemented within the Snowflake Native Apps

3. What are the component versions in the environment (pip freeze)?

   The Stored Procedure is executed within a Snowflake Native App environment. The specific versions of the installed packages are not directly accessible within the Native App context.

4. What did you do?

   I am attempting to execute a Stored Procedure implemented within a Snowflake Native App via a Snowpark Python Task. The Stored Procedure is designed to return a table, but it fails to execute when called from the Task.

   Steps to Reproduce:

   1. Create a Snowflake Native App containing a Stored Procedure that returns a table.
   2. Implement Task that calls the Stored Procedure.
   3. Execute the Task.

5. What did you expect to see?

   I expected the Task to successfully call the Stored Procedure and retrieve the resulting table.

   What happened instead?

   The Task fails with an error indicating that the Stored Procedure could not be executed. Additionally, attempting to retrieve the results using result_scan fails, suggesting that the SYSTEM user (used by Tasks) does not have permission to access the results within the Native App context.

It seems that the execution of the procedure is failing when trying to run the following code:

snowpark-python/src/snowflake/snowpark/_internal/server_connection.py

Line 452 in d640a36

new_cursor.execute(f"SELECT * FROM TABLE(RESULT_SCAN('{qid}'))")

6. Can you set logging to DEBUG and collect the logs?

Additional Information:

• This issue appears to be a bug specific to the interaction between Tasks and Native Apps when Stored Procedures return tables.
• The Stored Procedure functions correctly and returns the expected table when called directly (outside of a Task).

Request:

Please investigate and resolve this issue to enable the successful execution of Stored Procedures within Snowflake Native Apps when called from Snowpark Tasks, especially when those procedures return tables. Additionally, please address the permissions issue related to result retrieval to ensure seamless integration between these components.

[sfc-gh-dszmolka]

hi and thanks for raising this with us, and especially for providing all the details in advance! Took a quick look and reproduced the issue the following way:

1. created a super-simple stored procedure which return non-tabular data (LANGUAGE SQL) and installed as Native App as described in tutorial https://docs.snowflake.com/en/developer-guide/native-apps/tutorials/getting-started-tutorial#add-application-logic-and-install-your-first-application
2. created a Task which calls the procedure carried in the Native App. All is well, Task is running successfully
3. created a new Native App with this simple Python procedure:

CREATE OR REPLACE PROCEDURE addtwo_tabular(input NUMBER)
RETURNS TABLE(result NUMBER)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
AS
$$
from snowflake.snowpark import Row

def run(session, input):
   df = session.create_dataframe([Row(result=input + 2)])
   return df
$$;

and verified it works, outside of the Native App when created as standalone, and also when installed from Native App.
4. create new task which calls this procedure. Now the execution fails from the Task - per Query History:

call HELLO_SNOWFLAKE_APP_t.core.addtwo_tabular(2)
Python Interpreter Error: snowflake.snowpark.exceptions.SnowparkSQLException: 1304): 01b..9ba: 000709 (02000): Statement 01b..9b6 not found in function ADDTWO_TABULAR with handler run

Will investigate further.

[sfc-gh-dszmolka]

turns out this is a known limitation (internally tracked on SNOW-1050018) and there are efforts to address this by e.g. changing the security model to allow result_scan in additional use-cases to happen. Don't have any timeline unfortunately however, but will update this thread once any new info is known.

As a potential workaround i found suggestions creating procedure with EXECUTE AS CALLER but i'm really not sure if that addresses this specific use-case.