Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
ifoukarakis opened this issue Oct 17, 2024 · 1 comment
Open

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

ifoukarakis opened this issue Oct 17, 2024 · 1 comment
Labels
bug Something isn't working status-triage_done Initial triage done, will be further handled by the driver team

Comments

@ifoukarakis
Copy link

ifoukarakis commented Oct 17, 2024

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)
@ifoukarakis ifoukarakis added bug Something isn't working needs triage labels Oct 17, 2024
@github-actions github-actions bot changed the title MergeInto doesn't set bind variables SNOW-1746020: MergeInto doesn't set bind variables Oct 17, 2024
@sfc-gh-dszmolka sfc-gh-dszmolka added status-triage_done Initial triage done, will be further handled by the driver team and removed needs triage labels Oct 22, 2024
@sfc-gh-dszmolka
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working status-triage_done Initial triage done, will be further handled by the driver team
Projects
None yet
Development

No branches or pull requests

2 participants