SNOW-638839: MERGE INTO does not render argument correctly

[luciengaitskell]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   3.9.10

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

   Linux-5.10.0-16-cloud-amd64-x86_64-with-glibc2.31

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

   SQLAlchemy==1.4.31
   snowflake-connector-python==2.7.8
   snowflake-sqlalchemy==1.3.4
   

4. What did you do?

from snowflake.sqlalchemy import MergeInto, dialect
from sqlalchemy import Column, Integer, MetaData, Table, select
from sqlalchemy.orm import aliased

meta = MetaData()

target_table = Table(
    "target_table",
    meta,
    Column("a", Integer),
)

source_table = aliased(
    Table(
        "some_table",
        meta,
        Column("a", Integer),
        Column("b", Integer),
    ),
    name="source_table",
)

# Example subquery if more processing needed ontop:
source = select(
    source_table.c["a"].label("a"),
    source_table.c["b"].label("b"),
).subquery("source")

op = MergeInto(target_table, source, target_table.c.a == source.c.a)

print(op.compile(dialect=dialect()))

Result:

MERGE INTO target_table USING SELECT source_table.a AS a, source_table.b AS b 
FROM some_table AS source_table ON target_table.a = source.a

5. What did you expect to see?

MERGE INTO target_table USING (SELECT source_table.a AS a, source_table.b AS b 
FROM some_table AS source_table) AS source ON target_table.a = source.a

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

import logging
import os

for logger_name in ['snowflake.sqlalchemy', 'snowflake.connector']:
   logger = logging.getLogger(logger_name)
   logger.setLevel(logging.DEBUG)
   ch = logging.StreamHandler()
   ch.setLevel(logging.DEBUG)
   ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
   logger.addHandler(ch)

The problem is already debugged and addrssed in #321

[sfc-gh-mkeller]

recreate jira

[github-actions[bot]]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response

[ma0c]

For anybody having this issue, you can manually overwrite the behavior of visit_merge_into using a custom compiler, something like

from sqlalchemy.ext.compiler import compiles
from snowflake.sqlalchemy import MergeInto

@compiles(MergeInto, "snowflake")
def visit_merge_into(merge_into, compiler, **kw):
    clauses = " ".join(clause._compiler_dispatch(compiler, **kw) for clause in merge_into.clauses)
    source = merge_into.source._compiler_dispatch(compiler, **kw)
    on = merge_into.on._compiler_dispatch(compiler, **kw)
    return f"MERGE INTO {merge_into.target} USING ({source}) {merge_into.source.name} ON {on}" + (
        " " + clauses if clauses else ""
    )