SNOW-1746020: MergeInto doesn't set bind variables

[ifoukarakis]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

Python 3.10.10

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

macOS-14.5-arm64-arm-64bit

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

snowflake-connector-python==3.12.2
snowflake-sqlalchemy==1.5.1
SQLAlchemy==1.4.51

4. What did you do?

When using MergeInto with an on that contains a bind parameter, an exception is raised.

The following code helps reproduce the problem:

from datetime import timedelta, datetime

from sqlalchemy import Table, MetaData, Column, Integer, VARCHAR, create_engine, DateTime, and_
from snowflake.sqlalchemy import MergeInto

metadata = MetaData(schema="test_schema")

base_table = Table(
    'base_table',
    metadata,
    Column('id', Integer),
    Column('col_a', VARCHAR(100)),
    Column('ts', DateTime),
)

delta_table = Table(
    'delta_table',
    metadata,
    Column('id', Integer),
    Column('col_a', VARCHAR(100)),
    Column('ts', DateTime),
)
engine = create_engine(...)

with engine.connect() as conn:

    merge = MergeInto(
        target=base_table,
        source=delta_table,
        on=and_(base_table.c.id == delta_table.c.id, base_table.c.ts >= datetime.now() - timedelta(days=1)),
    )
    merge.when_matched_then_update().values(id=base_table.c.id)
    merge.when_not_matched_then_insert().values(id=delta_table.c.id, col_a=delta_table.c.col_a, ts=delta_table.c.ts)

    conn.execute(merge)

5. What did you expect to see?

   What should have happened and what happened instead?

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

Traceback (most recent call last):
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 1087, in execute
    Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 284, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 339, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 215, in default_errorhandler
    raise error_class(
snowflake.connector.errors.ProgrammingError: 002049 (42601): 01b7bf22-0511-d6d6-0004-ce0353ed22f2: SQL compilation error: error line 1 at position 175
Bind variable :ts_1 not set.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/user/snowflake-sqlalchemy-test/utils/db/bug_b.py", line 59, in <module>
    conn.execute(merge)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 1087, in execute
    Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 284, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 339, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/Users/user/snowflake-sqlalchemy-test/.venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 215, in default_errorhandler
    raise error_class(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002049 (42601): 01b7bf22-0511-d6d6-0004-ce0353ed22f2: SQL compilation error: error line 1 at position 175
Bind variable :ts_1 not set.
[SQL: MERGE INTO dev_user.base_table USING dev_user.delta_table ON dev_user.base_table.id = dev_user.delta_table.id AND dev_user.base_table.ts >= :ts_1 WHEN MATCHED THEN UPDATE SET id = dev_user.base_table.id WHEN NOT MATCHED THEN INSERT (id, col_a, ts) VALUES (dev_user.delta_table.id, dev_user.delta_table.col_a, dev_user.delta_table.ts)]
(Background on this error at: https://sqlalche.me/e/14/f405)

[sfc-gh-dszmolka]

thank you for raising this one too - we'll take a look.

[DanielWeitzenfeld]

I'm struggling to use MergeInto with source = a sqlalchemy subquery, and I think this may be the same bug. No bind params are sent, and literals are sent as "[POSTCOMPILE_param_1]."
If this is possible, could you add an example do the documentation?
If this is the same bug, please confirm so I can stop digging.
Thanks!