Skip to content

Postgres UPDATE...FROM in a CTE compilation error with 1.4 #6303

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.

Sign up for GitHub

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

Jump to bottom
Closed
dbluestein opened this issue Apr 17, 2021 · 2 comments
Closed

Postgres UPDATE...FROM in a CTE compilation error with 1.4 #6303

dbluestein opened this issue Apr 17, 2021 · 2 comments
Labels
bug Something isn't working postgresql regression something worked and was broken by a change sql
Milestone
1.4.x

Comments

@dbluestein
Copy link

Using an "UPDATE...FROM" in a CTE causes an error during compilation as of 1.4: TypeError: _compiler_dispatch() got multiple values for keyword argument 'asfrom'

from sqlalchemy import Column, Table, Integer, MetaData, String, select, create_engine

metadata = MetaData()

t1 = Table(
    'table_1', metadata,
    Column('id', Integer),
    Column('val', String)
)

t2 = Table(
    'table_2', metadata,
    Column('id', Integer),
    Column('val', String)
)

engine = create_engine('postgresql://')

upd = t1.update().values(
    val=t2.c.val
).where(
    t1.c.id == t2.c.id
).returning(
    t1.c.id, t1.c.val
)

print("update stmt:\n", upd.compile(engine))

cte = upd.cte('update_cte')

qry = select([cte])

print("\nstmt w/cte:\n", qry.compile(engine))

Running with 1.3.24:

update stmt:
 UPDATE table_1 SET val=table_2.val FROM table_2 WHERE table_1.id = table_2.id RETURNING table_1.id, table_1.val

stmt w/cte:
 WITH update_cte AS
(UPDATE table_1 SET val=table_2.val FROM table_2 WHERE table_1.id = table_2.id RETURNING table_1.id, table_1.val)
 SELECT update_cte.id, update_cte.val
FROM update_cte

and running with 1.4.8:

update stmt:
 UPDATE table_1 SET val=table_2.val FROM table_2 WHERE table_1.id = table_2.id RETURNING table_1.id, table_1.val
Traceback (most recent call last):
  File "cte.py", line 27, in <module>
    print("\nstmt w/cte:\n", qry.compile(engine))
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 506, in compile
    return self._compiler(dialect, **kw)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 570, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 766, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3098, in visit_select
    text = self._compose_select_body(
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3237, in _compose_select_body
    [
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3238, in <listcomp>
    f._compiler_dispatch(
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2564, in visit_cte
    inner = cte.element._compiler_dispatch(
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3673, in visit_update
    extra_from_text = self.update_from_clause(
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2423, in update_from_clause
    return "FROM " + ", ".join(
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2424, in <genexpr>
    t._compiler_dispatch(self, asfrom=True, fromhints=from_hints, **kw)
TypeError: _compiler_dispatch() got multiple values for keyword argument 'asfrom'

Stringifying the query without specifying a dialect gives a similar traceback, with the last two lines differing:

  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3884, in update_from_clause
    return "FROM " + ", ".join(
  File "/home/dblue/sqla/envl/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3885, in <genexpr>
    t._compiler_dispatch(self, asfrom=True, fromhints=from_hints, **kw)
TypeError: _compiler_dispatch() got multiple values for keyword argument 'asfrom'

Tested with python 3.9 on macOS and python 3.8 on linux.
@sqla-tester
Copy link
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

pass asfrom correctly in compilers https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2754

@zzzeek zzzeek added sql bug Something isn't working regression something worked and was broken by a change labels Apr 17, 2021
@zzzeek zzzeek added this to the 1.4.x milestone Apr 17, 2021
@zzzeek
Copy link
Member

zzzeek commented Apr 17, 2021

thanks for reporting!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working postgresql regression something worked and was broken by a change sql
Projects
None yet
Milestone
1.4.x
Development

No branches or pull requests
3 participants
@zzzeek @dbluestein @sqla-tester