[Sqlalchemy-tickets] Issue #3440: Problem with selects using CTEs inside function constructs (zzzeek/sqlalchemy)

[Konsta V. <iss...@bi...>]

New issue 3440: Problem with selects using CTEs inside function constructs
https://bitbucket.org/zzzeek/sqlalchemy/issue/3440/problem-with-selects-using-ctes-inside

Konsta Vesterinen:

I've been having problems with CTEs inside function constructs. The CTEs seem to get duplicated in the parent query FROM part. This in turn causes variety of problems. For now I've been using workarounds (in other words not using CTEs at all). However now I have a scenario where this feature desperately needed.

I created a simplistic scenario which illustrates this problem. The generated SQL is also wrong (at least in PostgreSQL) since the select clause in from part does not have an alias.


```
#!python

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


engine = sa.create_engine(
    'postgres://postgres@localhost/sqlalchemy_utils_test'
)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    def __repr__(self):
        return 'User(name=%r)' % self.name

Base.metadata.create_all(bind=session.bind)

users = sa.select([User.name]).limit(1).cte('users')

query = sa.select(
    [
        sa.func.json_build_object('user', sa.select([users.c.name]))
    ]
)
# query IS:
#
# WITH users AS
# (SELECT "user".name AS name
# FROM "user"
#  LIMIT :param_1)
#  SELECT json_build_object(:json_build_object_2, (SELECT users.name
# FROM users)) AS json_build_object_1
# FROM (SELECT users.name AS name
# FROM users)
#
# query SHOULD BE:
#
# WITH users AS
# (SELECT "user".name AS name
# FROM "user"
#  LIMIT 1)
#  SELECT json_build_object('something', (SELECT users.name FROM users))

session.execute('DROP TABLE "user"')
session.commit()

```
There might also be a simple workaround for this that I just haven't noticed.