SNOW-1675555: Snowflake Pivot Fails to bind parameter in CTE when pivoted

[snoe]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of JDBC driver are you using?
   Reproduces on 3.19.0 and 3.15.1

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

3. What version of Java are you using?
   11.0.22

4. What did you do?

Based on metabase/metabase#45817 I reproduced that issue with straight JDBC calls. The bug seems to lie the shape of the query and trying to bind a preparedStatement parameter when it appears within the CTE and is then pivoted.

Query 1: This query fails:

with select_stuff as (
  select * from (values (1,'a'), (2,'b')) as x("id","state")
  where "state" = ?)
select * from select_stuff
PIVOT(count("state") for "state" in (SELECT DISTINCT "state" FROM select_stuff))

The exception is:

Execution error (SnowflakeSQLException) at net.snowflake.client.jdbc.SnowflakeUtil/checkErrorAndThrowExceptionSub (SnowflakeUtil.java:172).
SQL compilation error: error line 2 at position 42
Bind variable ? not set.

Query 2: This query succeeds (hardcoded the param)

with select_stuff as (
  select * from (values (1,'a'), (2,'b')) as x("id","state")
  where "state" = 'a'
)
select * from select_stuff
PIVOT(count("state") for "state" in (SELECT DISTINCT "state" FROM select_stuff))

Query 3: This query succeeds (moved the where)

with select_stuff as (
  select * from (values (1,'a'), (2,'b')) as x("id","state")
)
select * from select_stuff
PIVOT(count("state") for "state" in (SELECT DISTINCT "state" FROM select_stuff   where "state" = ?))

Query 4: This query succeeds (removed the pivot):

with select_stuff as (
  select * from (values (1,'a'), (2,'b')) as x("id","state")
  where "state" = ?)
select * from select_stuff

The clojure code I used to test these queries.

(mt/test-driver
    :snowflake
    (jdbc/with-db-connection
      [conn (sql-jdbc.conn/connection-details->spec :snowflake (:details (mt/db)))]
      (with-open [stmt (.prepareStatement
                         (:connection conn)
                         "with select_stuff as (
                          select * from (values (1,'a'), (2,'b')) as x(\"id\",\"state\")
                          where \"state\" = ?)
                          select * from select_stuff
                          PIVOT(count(\"state\") for \"state\" in (SELECT DISTINCT \"state\" FROM select_stuff))")]
        (.setString stmt 1 "a")
        (with-open [rs (.executeQuery stmt)]
          (resultset-seq rs)))))

5. What did you expect to see?

   Query 1 should succeed with the same results as Query 2.

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

If it's necessary, I can.

[paoliniluis]

Sorry to ping you all @sfc-gh-wfateem @sfc-gh-aaaggarwal @sfc-gh-aaarora @sfc-gh-aahmadvand @sfc-gh-aalam, can anyone help here?

[sfc-gh-aalam]

also tagging in @sfc-gh-dprzybysz

[sfc-gh-wfateem]

Thanks for the ping @paoliniluis @snoe
I'll take a look and get back to you soon.

[sfc-gh-wfateem]

Based on the examples and the errors you shared, I had the suspicion that this wasn't a JDBC driver issue but rather an issue at the backend. I tested something similar in Python and I was able to reproduce the issue:

try:
    cs.execute('''
         with select_stuff as (
           select * from (values (1,'a'), (2,'b')) as x("id","state")
           where "state" = ?)
         select * from select_stuff
         PIVOT(count("state") for "state" in (SELECT DISTINCT "state" FROM select_stuff))''', (['a'])
    )

That resulted in the same error you reported here:

MainThread cursor.py:1071 - execute() - DEBUG - {'data': {'internalError': False, 'unredactedFromSecureObject': False, 'errorCode': '002049', 'age': 0, 'sqlState': '42601', 'queryId': '01b79a4f-0411-ba4f-006e-3283005ec886', 'line': -1, 'pos': -1, 'type': 'COMPILATION'}, 'code': '002049', 'message': 'SQL compilation error: error line 2 at position 27\nBind variable ? not set.', 'success': False, 'headers': None}

I was even able to reproduce the issue with the SQL API. This was the body of my HTTP request:

{
  "statement": "with select_stuff as (select * from (values (1,'a'), (2,'b')) as x(\"id\",\"state\") where \"state\" = ?) select * from select_stuff PIVOT(count(\"state\") for \"state\" in (SELECT DISTINCT \"state\" FROM select_stuff))",
  "bindings": {
  "1": {
    "type": "TEXT",
    "value": "a"
    }
  }
}

This was the response:

{
    "code": "002049",
    "message": "SQL compilation error: error line 1 at position 97\nBind variable ? not set.",
    "sqlState": "42601",
    "statementHandle": "01b79bd8-0411-ba4b-006e-3283005ee8de"
}

I would suggest you consider opening a case directly with the Snowflake Support team to look into this particular issue.

[paoliniluis]

thanks so much @sfc-gh-wfateem, as always

[sfc-gh-wfateem]

@paoliniluis @snoe, since this isn't a JDBC driver issue, I will go ahead and close this off for now.

Please do reach out to Snowflake Support and raise this issue if it's something you need help with.