[Sqlalchemy-tickets] Issue #3821: alias of cte not compiling correctly (zzzeek/sqlalchemy)

[Joerg R. <iss...@bi...>]

New issue 3821: alias of cte not compiling correctly
https://bitbucket.org/zzzeek/sqlalchemy/issues/3821/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:

```
#!python

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
```