Problem with selects using CTEs inside function constructs
Issue #3440
resolved
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.
Comments (4)
-
repo owner -
repo owner -
reporter Wow thanks for a very quick reply. It seems to work, thanks!
-
repo owner - changed status to resolved
we'll stick with that for now.
- Log in to comment
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 comes out as:
worksforme?