SNOW-3198369: "Statement provided can not be prepared" error when using session.sql to set a SQL variable without .collect()

[sfc-gh-vtimofeenko]

1. What version of Python are you using?

   Python 3.11.6 (v3.11.6:8b6ee5ba3b, Oct 2 2023, 11:18:21) [Clang 13.0.0 (clang-1300.0.29.30)]

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

   macOS-26.3-arm64-arm-64bit

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

   uv pip freeze | egrep 'streamlit|snowflake|snowpark'
   snowflake-connector-python==4.3.0
   snowflake-snowpark-python==1.45.0
   streamlit==1.54.0
   

4. What did you do?

   I was working with Streamlit in Snowflake on SPCS examining how SQL session variables behave. I found that if my query is SET foo = 'bar' and I just st.write it (without calling collect()), I get an odd error "Statement provided can not be prepared."

   If I do .collect() it, there are no errors and the variable works.

   The relevant code is: st.write(session.sql("SET foo = 'bar'")), full code of the application is provided at the end. I reproduced this in SPCS and locally.

5. What did you expect to see?

   st.write generally materializes the session.sql result without the need to explicitly .collect() it. I would expect that the statement st.write(session.sql("SET foo = 'bar'")) would work without an error.

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

Debug logs attached.

debug-logs-session-collect.log

Streamlit code

"""Testing materialization of st.write with differently constructed Streamlit sessions."""

import logging

import snowflake.snowpark
import streamlit as st

for logger_name in ("snowflake.snowpark", "snowflake.connector"):
    logger = logging.getLogger(logger_name)
    logger.setLevel(logging.DEBUG)
    ch = logging.StreamHandler()
    ch.setLevel(logging.DEBUG)
    ch.setFormatter(
        logging.Formatter(
            "%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s"
        )
    )
    logger.addHandler(ch)

st.caption(f"snowpark {snowflake.snowpark.__version__}")

STMTS = ("SELECT CURRENT_ACCOUNT()", "SET foo = 'bar'", "SELECT $foo")  # Just to check that we're actually live

METHODS = (
    "conn.cur.exec",
    "session.sql.collect",
    "session.sql.no-collect",
    "query",
)


def id_(it):
    """Identity function."""
    return it


conn = st.connection("snowflake")

for method in METHODS:
    # In this code:
    # - `dispatcher` is the object that will (maybe, lazily) execute the query
    # - `materializer` will explicitly materialize the function or pass it as is (using `id_`)
    try:
        st.subheader(method)
        match method.split(".")[0]:
            case "conn":
                dispatcher = conn.cursor().execute
                materializer = id_
            case "session":
                dispatcher = conn.session().sql
                if method.split(".")[2] == "collect":
                    materializer = lambda it: it.collect()
                else:
                    materializer = id_
            case "query":
                dispatcher = conn.query
                materializer = id_

            case _:
                raise RuntimeError("HACF")

        for stmt in STMTS:
            st.code(stmt)
            st.write(materializer(dispatcher(stmt)))

    except Exception as e:
        st.error(e)
        st.error(e.__dict__)

Looks like this patch to snowpark-python fixes the behavior, but I am not sure if it has any side effects:

diff --git a/src/snowflake/snowpark/_internal/analyzer/schema_utils.py b/src/snowflake/snowpark/_internal/analyzer/schema_utils.py
index f0aa29bba..65798cb52 100644
--- a/src/snowflake/snowpark/_internal/analyzer/schema_utils.py
+++ b/src/snowflake/snowpark/_internal/analyzer/schema_utils.py
@@ -77,7 +77,7 @@ def analyze_attributes(
     # SQL commands which cannot be prepared
     # https://docs.snowflake.com/en/user-guide/sql-prepare.html
     if lowercase.startswith(
-        ("alter", "drop", "use", "create", "grant", "revoke", "comment")
+        ("alter", "drop", "use", "create", "grant", "revoke", "comment", "set")
     ):
         return command_attributes()
     if lowercase.startswith(("ls", "list")):

[sfc-gh-sghosh]

Hi @sfc-gh-vtimofeenko ,

Thanks for raising the issue.
We are able to reproduce the issue, will work on eliminating it.

It seems, SET (and UNSET) are missing from the non-preparable command list in schema_utils.py:analyze_attributes(). When Snowpark needs to infer schema (for .to_pandas(), .schema, .show(), Streamlit's st.write()), it tries to DESCRIBE/prepare the SET statement, which Snowflake rejects with error 0A000: Statement provided can not be prepared.