<p dir="auto"><strong>Migrated issue, originally created by Konsta Vesterinen (<a href="https://github.com/kvesteri">@kvesteri</a>)</strong></p>
<p dir="auto">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.</p>
<p dir="auto">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.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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 &quot;user&quot;.name AS name
# FROM &quot;user&quot;
#  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 &quot;user&quot;.name AS name
# FROM &quot;user&quot;
#  LIMIT 1)
#  SELECT json_build_object('something', (SELECT users.name FROM users))

session.execute('DROP TABLE &quot;user&quot;')
session.commit()
"><pre class="notranslate"><code class="notranslate">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()

</code></pre></div>
<p dir="auto">There might also be a simple workaround for this that I just haven't noticed.</p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">fdb.py supports firebird under python2/3</p>
<p dir="auto">There are only minor changes to the existing kinterbasdb driver.</p>
<p dir="auto">A patch is attached.</p>
<p dir="auto">It works for me using python2 and firebird-2.5.2</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/2504/fdbsupport.diff">fdbsupport.diff</a></p>