SNOW-974954: Binding variables are dropped when executing multiple statements

[dylangrandmont]

I am seeing behaviour with the driver wherein binding variables appear to be dropped when executing multiple statements. See an example snippet below. I receive errors of the form

    Error [OperationFailedError]: JavaScript execution error: Uncaught Execution of multiple statements failed on statement "INSERT INTO ..." (at line 6, position 4).
    SQL compilation error: error line 2 at position 42
    Bind variable :1 not set. in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4
    stackstrace: 
    SYSTEM$MULTISTMT line: 10

1. What version of NodeJS driver are you using?
   1.9.1

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

3. What version of NodeJS are you using?
   v18.18.0

5.Server version:* E.g. 1.90.1
You may get the server version by running a query:

SELECT CURRENT_VERSION();

6. What did you do?

Here is an example snippet

connection.execute({
  sqlText: 'INSERT INTO table_1(id)\n' +
          '    VALUES (1);\n' +
          '    INSERT INTO table_2t(id)\n' +
          "    SELECT :1;\n",
  binds: [2],
  parameters: { MULTI_STATEMENT_COUNT: 2 }
});

7. What did you expect to see?

Query should execute without error

[sfc-gh-dszmolka]

hi and thank you for submitting this issue - we're going to take a look at it

[sfc-gh-dszmolka]

seems to work correctly with

  sqlText: 'INSERT INTO test_db.node704.table_1(id)\n' +
          '    VALUES (1);\n' +
          '    INSERT INTO test_db.node704.table_2t(id)\n' +
          "    SELECT ?;\n",
  binds: [2],
  parameters: { MULTI_STATEMENT_COUNT: 2 },

using ? for variable binding instead of :1. i'm checking further how this behaves with other Snowflake drivers.

[sfc-gh-dszmolka]

using latest Snowflake Python Connector 3.5.0 on Python 3.10, with below repro script:

import snowflake.connector

# https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-example#qmark-or-numeric-binding
snowflake.connector.paramstyle='numeric'

con = snowflake.connector.connect(
    user='user',
    password='password',
    account='myaccount.eu-central-1',
    warehouse='COMPUTE_WH'
)
query = "INSERT INTO test_db.node704.table_1(id) VALUES(1); INSERT INTO test_db.node704.table_2t(id) SELECT :1"

res = con.cursor().execute(query, [2], num_statements=2)

# con.cursor().execute(<multiple statements>).fetchall() doesnt work, known issue
# https://github.com/snowflakedb/snowflake-connector-python/issues/1604
while res.nextset():
    print(res.fetchone())

produces

# python test.py 
Traceback (most recent call last):
  File "/test/test.py", line 14, in <module>
    res = con.cursor().execute(query, [2], num_statements=2)
  File "/usr/local/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 920, in execute
    Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
  File "/usr/local/lib/python3.10/site-packages/snowflake/connector/errors.py", line 290, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
  File "/usr/local/lib/python3.10/site-packages/snowflake/connector/errors.py", line 345, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/usr/local/lib/python3.10/site-packages/snowflake/connector/errors.py", line 221, in default_errorhandler
    raise error_class(
snowflake.connector.errors.ProgrammingError: 100132 (P0000): JavaScript execution error: Uncaught Execution of multiple statements failed on statement "INSERT INTO test_db.node704.ta..." (at line 1, position 51).
SQL compilation error: error line 1 at position 48
Bind variable :1 not set. in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4
stackstrace: 
SYSTEM$MULTISTMT line: 10

changing repro script to

...
snowflake.connector.paramstyle='qmark'
...
query = "INSERT INTO test_db.node704.table_1(id) VALUES(1); INSERT INTO test_db.node704.table_2t(id) SELECT ?"

(again, nothing else just ? instead of :1) produces:

# python test.py 
(1,)

and data is correctly inserted into the target table. Behaves exactly like the Node.JS driver.

While this behaviour might be unexpected, it looks like more like related to the Snowflake engine internals, rather than the Snowflake Node.JS driver here since with a totally different programming language, different Snowflake driver, the result is exactly the same.

edit: since this behaviour is not related to Snowflake Node.JS driver, closing this one. If you think the behaviour is still specific to this library, please update this ticket with the details and I'll reopen to investigate further.

[dylangrandmont]

Thanks for investigating this @sfc-gh-dszmolka . Using ? syntax for binding indeed seems to work so thanks for pointing that out to me. This seems to be behaviour specific to multi-statements. Is there a more "core" repo I can open a ticket in? Or is it possible to document this behaviour in the snowflake docs at least? This is non-obvious behaviour.

[sfc-gh-dszmolka]

i'll take this further with the folks responsible for the engine, because since my last comment it also came to my attention that the exact same issue persist with a third (.NET) Snowflake driver - which further confirms it's not driver related, and also that we'd need to address this in a more central location.

If you are a Snowflake customer (or even a trial user) you can file an official support ticket with Snowflake Support if you wish, but I'll take this up anyways with the engine dev team.

[sfc-gh-dszmolka]

found this SQL API doc https://docs.snowflake.com/en/developer-guide/sql-api/submitting-requests#using-bind-variables-in-a-statement

Snowflake does not currently support variable binding in multi-statement SQL requests.

which seems to be what happens here (and maybe it's the ? -style working what is unexpected :) ) . I'm confirming this with the engine team and if it's really expected what we see here, then we can perhaps proceed to update the drivers' documentation.