alias of cte not compiling correctly
Issue #3821
closed
The compiling of statements with aliases of CTEs seems kind of broken, e.g. the following example:
from sqlalchemy import *
from sqlalchemy.sql.compiler import SQLCompiler
from sqlalchemy.engine.default import DefaultDialect
tables = Table('my_table', MetaData(), Column('id', Integer))
cte = select(tables.columns).cte("cte")
alias1 = cte.alias('a1')
alias2 = cte.alias('a2')
query = select(
columns=[alias1.c.id, alias2.c.id],
from_obj=alias1.join(alias2, onclause=alias1.c.id==alias2.c.id))
print(query)
# WITH cte AS
# (SELECT my_table.id AS id
# FROM my_table)
# SELECT a1.id, a2.id
# FROM cte AS a1 JOIN cte AS a2 ON a1.id = a2.id
def raw_sql(query):
dialect = DefaultDialect()
compiler = SQLCompiler(dialect, query)
return compiler.process(query)
print(raw_sql(query))
# WITH cte AS
# (SELECT my_table.id AS id
# FROM my_table)
# SELECT a1.id, a2.id
# FROM a1 JOIN a2 ON a1.id = a2.id
Comments (3)
-
repo owner -
repo owner because...basically the example above is mis-using the API. the SQLCompiler does the string inside the constructor, and it's complete. Calling process() again is failing because the ".ctes" dictionary is already populated.
-
repo owner - changed status to closed
reopen if you meant that both statements are wrong somehow, otherwise raw_sql() should read:
def raw_sql(query): dialect = DefaultDialect() compiler = SQLCompiler(dialect, query) return str(compiler)
it's not surprising that process() is not working on a second run, isn't meant to be used like that.
- Log in to comment
can you confirm print(query) is what you expect and whatever is going on in print(raw_sql(query)) is not ?