[Sqlalchemy-tickets] Issue #3440: Problem with selects using CTEs inside function constructs (zzzee
Brought to you by:
zzzeek
|
From: Konsta V. <iss...@bi...> - 2015-06-04 11:25:04
|
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. |