search

snowflakedb / snowflake-sqlalchemy

Snowflake SQLAlchemy
https://pypi.python.org/pypi/snowflake-sqlalchemy/
Apache License 2.0
235 stars 152 forks source link

SNOW-1746020: MergeInto doesn't set bind variables #536

Open ifoukarakis opened 1 month ago

ifoukarakis commented 1 month ago

Please answer these questions before submitting your issue. Thanks!

  1. What version of Python are you using?

Python 3.10.10

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

macOS-14.5-arm64-arm-64bit

  1. 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
  1. 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)

  1. What did you expect to see?

    What should have happened and what happened instead?

  2. 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 commented 1 month ago

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