Problem with selects using CTEs inside function constructs

[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.

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.

[Mike Bayer]

the SQL function is a column-oriented query and a SELECT inside of it needs to be column-oriented as well. We could in theory coerce this automatically (would rather not at this point, explicit is better than implicit anyway) but this works:

query = sa.select(
    [
        sa.func.json_build_object('user', sa.select([users.c.name]).as_scalar())
    ]
)

query comes out as:

WITH users AS 
(SELECT "user".name AS name 
FROM "user" 
 LIMIT %(param_1)s)
 SELECT json_build_object(%(json_build_object_2)s, (SELECT users.name 
FROM users)) AS json_build_object_1

worksforme?

[Konsta Vesterinen]

Wow thanks for a very quick reply. It seems to work, thanks!

[Mike Bayer]

we'll stick with that for now.