alias of cte not compiling correctly

[Joerg Rittinger]

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

[Mike Bayer]

can you confirm print(query) is what you expect and whatever is going on in print(raw_sql(query)) is not ?

[Mike Bayer]

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.

[Mike Bayer]

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.