<p dir="auto"><strong>Migrated issue, originally created by Adrian (<a href="https://github.com/thiefmaster">@thiefmaster</a>)</strong></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.dialects.postgresql import ARRAY, array
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *


def _patch_sa_cte_fix():
    import pkg_resources
    sa_dist = pkg_resources.get_distribution('sqlalchemy')
    if sa_dist.parsed_version &gt; pkg_resources.parse_version('1.0.12'):
        raise Exception('Remove this monkeypatch; SQLAlchemy contains the CTE fix')
    from sqlalchemy.sql.selectable import CTE
    from sqlalchemy.sql.elements import _clone
    def _copy_internals(self, clone=_clone, **kw):
        super(CTE, self)._copy_internals(clone, **kw)
        if self._cte_alias is not None:
            self._cte_alias = self
        self._restates = frozenset([clone(elem, **kw) for elem in self._restates])
    CTE._copy_internals = _copy_internals

_patch_sa_cte_fix()
Base = declarative_base()


class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    parent_id = Column(
        Integer,
        ForeignKey('categories.id'),
        index=True,
        nullable=True
    )
    is_deleted = Column(
        Boolean,
        nullable=False,
        default=False
    )
    children = relationship(
        'Category',
        primaryjoin=(id == remote(parent_id)) &amp; ~remote(is_deleted),
        lazy=True,
        backref=backref(
            'parent',
            primaryjoin=(remote(id) == parent_id),
            lazy=True
        )
    )

    @staticmethod
    def _create_column_prop():
        cat_alias = aliased(Category)
        cte_query = (select([cat_alias.id, cast(array([]), ARRAY(Integer)).label('parents')])
                     .where(cat_alias.parent_id.is_(None) &amp; ~cat_alias.is_deleted)
                     .cte(recursive=True))
        parent_query = (select([cat_alias.id, cte_query.c.parents.op('||')(cat_alias.parent_id)])
                        .where((cat_alias.parent_id == cte_query.c.id) &amp; ~cat_alias.is_deleted))
        cte_query = cte_query.union_all(parent_query)
        query = select([func.count()]).where(cte_query.c.parents.contains(array([Category.id])))
        Category.deep_children_count = column_property(query, deferred=True)


Category._create_column_prop()

e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)


root = Category(id=0, children=[
    Category(id=1),
    Category(id=2, children=[
        Category(id=3),
        Category(id=4),
        Category(id=5, children=[
            Category(id=6),
            Category(id=7)
        ])
    ])
])
s.add(root)
s.flush()
s.expire_all()

print

cat = s.query(Category).options(undefer('deep_children_count'),
                                joinedload('children').undefer('deep_children_count')).get(2)
print cat
print cat.deep_children_count
print cat.children"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-c1">*</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">dialects</span>.<span class="pl-s1">postgresql</span> <span class="pl-k">import</span> <span class="pl-v">ARRAY</span>, <span class="pl-s1">array</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-c1">*</span>


<span class="pl-k">def</span> <span class="pl-en">_patch_sa_cte_fix</span>():
    <span class="pl-k">import</span> <span class="pl-s1">pkg_resources</span>
    <span class="pl-s1">sa_dist</span> <span class="pl-c1">=</span> <span class="pl-s1">pkg_resources</span>.<span class="pl-en">get_distribution</span>(<span class="pl-s">'sqlalchemy'</span>)
    <span class="pl-k">if</span> <span class="pl-s1">sa_dist</span>.<span class="pl-s1">parsed_version</span> <span class="pl-c1">&gt;</span> <span class="pl-s1">pkg_resources</span>.<span class="pl-en">parse_version</span>(<span class="pl-s">'1.0.12'</span>):
        <span class="pl-k">raise</span> <span class="pl-v">Exception</span>(<span class="pl-s">'Remove this monkeypatch; SQLAlchemy contains the CTE fix'</span>)
    <span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">sql</span>.<span class="pl-s1">selectable</span> <span class="pl-k">import</span> <span class="pl-v">CTE</span>
    <span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">sql</span>.<span class="pl-s1">elements</span> <span class="pl-k">import</span> <span class="pl-s1">_clone</span>
    <span class="pl-k">def</span> <span class="pl-en">_copy_internals</span>(<span class="pl-s1">self</span>, <span class="pl-s1">clone</span><span class="pl-c1">=</span><span class="pl-s1">_clone</span>, <span class="pl-c1">**</span><span class="pl-s1">kw</span>):
        <span class="pl-en">super</span>(<span class="pl-v">CTE</span>, <span class="pl-s1">self</span>).<span class="pl-en">_copy_internals</span>(<span class="pl-s1">clone</span>, <span class="pl-c1">**</span><span class="pl-s1">kw</span>)
        <span class="pl-k">if</span> <span class="pl-s1">self</span>.<span class="pl-s1">_cte_alias</span> <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-c1">None</span>:
            <span class="pl-s1">self</span>.<span class="pl-s1">_cte_alias</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>
        <span class="pl-s1">self</span>.<span class="pl-s1">_restates</span> <span class="pl-c1">=</span> <span class="pl-en">frozenset</span>([<span class="pl-en">clone</span>(<span class="pl-s1">elem</span>, <span class="pl-c1">**</span><span class="pl-s1">kw</span>) <span class="pl-k">for</span> <span class="pl-s1">elem</span> <span class="pl-c1">in</span> <span class="pl-s1">self</span>.<span class="pl-s1">_restates</span>])
    <span class="pl-v">CTE</span>.<span class="pl-s1">_copy_internals</span> <span class="pl-c1">=</span> <span class="pl-s1">_copy_internals</span>

<span class="pl-en">_patch_sa_cte_fix</span>()
<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()


<span class="pl-k">class</span> <span class="pl-v">Category</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'categories'</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">parent_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(
        <span class="pl-v">Integer</span>,
        <span class="pl-v">ForeignKey</span>(<span class="pl-s">'categories.id'</span>),
        <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
        <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>
    )
    <span class="pl-s1">is_deleted</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(
        <span class="pl-v">Boolean</span>,
        <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>,
        <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-c1">False</span>
    )
    <span class="pl-s1">children</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(
        <span class="pl-s">'Category'</span>,
        <span class="pl-s1">primaryjoin</span><span class="pl-c1">=</span>(<span class="pl-s1">id</span> <span class="pl-c1">==</span> <span class="pl-en">remote</span>(<span class="pl-s1">parent_id</span>)) <span class="pl-c1">&amp;</span> <span class="pl-c1">~</span><span class="pl-en">remote</span>(<span class="pl-s1">is_deleted</span>),
        <span class="pl-s1">lazy</span><span class="pl-c1">=</span><span class="pl-c1">True</span>,
        <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-en">backref</span>(
            <span class="pl-s">'parent'</span>,
            <span class="pl-s1">primaryjoin</span><span class="pl-c1">=</span>(<span class="pl-en">remote</span>(<span class="pl-s1">id</span>) <span class="pl-c1">==</span> <span class="pl-s1">parent_id</span>),
            <span class="pl-s1">lazy</span><span class="pl-c1">=</span><span class="pl-c1">True</span>
        )
    )

    <span class="pl-en">@<span class="pl-s1">staticmethod</span></span>
    <span class="pl-k">def</span> <span class="pl-en">_create_column_prop</span>():
        <span class="pl-s1">cat_alias</span> <span class="pl-c1">=</span> <span class="pl-en">aliased</span>(<span class="pl-v">Category</span>)
        <span class="pl-s1">cte_query</span> <span class="pl-c1">=</span> (<span class="pl-en">select</span>([<span class="pl-s1">cat_alias</span>.<span class="pl-s1">id</span>, <span class="pl-en">cast</span>(<span class="pl-en">array</span>([]), <span class="pl-v">ARRAY</span>(<span class="pl-v">Integer</span>)).<span class="pl-en">label</span>(<span class="pl-s">'parents'</span>)])
                     .<span class="pl-en">where</span>(<span class="pl-s1">cat_alias</span>.<span class="pl-s1">parent_id</span>.<span class="pl-en">is_</span>(<span class="pl-c1">None</span>) <span class="pl-c1">&amp;</span> <span class="pl-c1">~</span><span class="pl-s1">cat_alias</span>.<span class="pl-s1">is_deleted</span>)
                     .<span class="pl-en">cte</span>(<span class="pl-s1">recursive</span><span class="pl-c1">=</span><span class="pl-c1">True</span>))
        <span class="pl-s1">parent_query</span> <span class="pl-c1">=</span> (<span class="pl-en">select</span>([<span class="pl-s1">cat_alias</span>.<span class="pl-s1">id</span>, <span class="pl-s1">cte_query</span>.<span class="pl-s1">c</span>.<span class="pl-s1">parents</span>.<span class="pl-en">op</span>(<span class="pl-s">'||'</span>)(<span class="pl-s1">cat_alias</span>.<span class="pl-s1">parent_id</span>)])
                        .<span class="pl-en">where</span>((<span class="pl-s1">cat_alias</span>.<span class="pl-s1">parent_id</span> <span class="pl-c1">==</span> <span class="pl-s1">cte_query</span>.<span class="pl-s1">c</span>.<span class="pl-s1">id</span>) <span class="pl-c1">&amp;</span> <span class="pl-c1">~</span><span class="pl-s1">cat_alias</span>.<span class="pl-s1">is_deleted</span>))
        <span class="pl-s1">cte_query</span> <span class="pl-c1">=</span> <span class="pl-s1">cte_query</span>.<span class="pl-en">union_all</span>(<span class="pl-s1">parent_query</span>)
        <span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>([<span class="pl-s1">func</span>.<span class="pl-en">count</span>()]).<span class="pl-en">where</span>(<span class="pl-s1">cte_query</span>.<span class="pl-s1">c</span>.<span class="pl-s1">parents</span>.<span class="pl-en">contains</span>(<span class="pl-en">array</span>([<span class="pl-v">Category</span>.<span class="pl-s1">id</span>])))
        <span class="pl-v">Category</span>.<span class="pl-s1">deep_children_count</span> <span class="pl-c1">=</span> <span class="pl-en">column_property</span>(<span class="pl-s1">query</span>, <span class="pl-s1">deferred</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-v">Category</span>.<span class="pl-en">_create_column_prop</span>()

<span class="pl-s1">e</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'postgresql:///test'</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">e</span>)
<span class="pl-s1">s</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>(<span class="pl-s1">e</span>)


<span class="pl-s1">root</span> <span class="pl-c1">=</span> <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">0</span>, <span class="pl-s1">children</span><span class="pl-c1">=</span>[
    <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">1</span>),
    <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">2</span>, <span class="pl-s1">children</span><span class="pl-c1">=</span>[
        <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">3</span>),
        <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">4</span>),
        <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">5</span>, <span class="pl-s1">children</span><span class="pl-c1">=</span>[
            <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">6</span>),
            <span class="pl-v">Category</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">7</span>)
        ])
    ])
])
<span class="pl-s1">s</span>.<span class="pl-en">add</span>(<span class="pl-s1">root</span>)
<span class="pl-s1">s</span>.<span class="pl-en">flush</span>()
<span class="pl-s1">s</span>.<span class="pl-en">expire_all</span>()

<span class="pl-s1">print</span>

<span class="pl-s1">cat</span> <span class="pl-c1">=</span> <span class="pl-s1">s</span>.<span class="pl-en">query</span>(<span class="pl-v">Category</span>).<span class="pl-en">options</span>(<span class="pl-en">undefer</span>(<span class="pl-s">'deep_children_count'</span>),
                                <span class="pl-en">joinedload</span>(<span class="pl-s">'children'</span>).<span class="pl-en">undefer</span>(<span class="pl-s">'deep_children_count'</span>)).<span class="pl-en">get</span>(<span class="pl-c1">2</span>)
<span class="pl-k">print</span> <span class="pl-s1">cat</span>
<span class="pl-k">print</span> <span class="pl-s1">cat</span>.<span class="pl-s1">deep_children_count</span>
<span class="pl-k">print</span> <span class="pl-s1">cat</span>.<span class="pl-s1">children</span></pre></div>
<blockquote>
<p dir="auto">sqlalchemy.exc.CompileError: Multiple, unrelated CTEs found with the same name: u'anon_2'</p>
</blockquote>
<p dir="auto">Removing one of the undefers or using subqueryload instead of joinedload works, but it still looks like a bug to me.</p>
<p dir="auto">Full traceback:</p>
<div class="highlight highlight-text-python-traceback notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;satest.py&quot;, line 90, in &lt;module&gt;
    joinedload('children').undefer('deep_children_count')).get(2)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py&quot;, line 831, in get
    return self._get_impl(ident, loading.load_on_ident)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py&quot;, line 864, in _get_impl
    return fallback_fn(self, key)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py&quot;, line 219, in load_on_ident
    return q.one()
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py&quot;, line 2693, in one
    ret = list(self)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py&quot;, line 2736, in __iter__
    return self._execute_and_instances(context)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py&quot;, line 2751, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 914, in execute
    return meth(self, multiparams, params)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py&quot;, line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1003, in _execute_clauseelement
    inline=len(distilled_params) &gt; 1)
  File &quot;&lt;string&gt;&quot;, line 1, in &lt;lambda&gt;
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py&quot;, line 494, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py&quot;, line 500, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 395, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 190, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py&quot;, line 81, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 1585, in visit_select
    for name, column in select._columns_plus_names
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 1357, in _label_select_column
    **column_clause_args
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py&quot;, line 93, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 615, in visit_label
    OPERATORS[operators.as_] + \
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py&quot;, line 93, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 527, in visit_grouping
    return &quot;(&quot; + grouping.element._compiler_dispatch(self, **kwargs) + &quot;)&quot;
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py&quot;, line 81, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 1608, in visit_select
    text, select, inner_columns, froms, byfrom, kwargs)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 1700, in _compose_select_body
    for f in froms])
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py&quot;, line 81, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py&quot;, line 1194, in visit_cte
    cte_name)
sqlalchemy.exc.CompileError: Multiple, unrelated CTEs found with the same name: u'anon_2'"><pre class="notranslate">Traceback (most recent call last):
  File <span class="pl-s">"satest.py"</span>, line <span class="pl-c1">90</span>, in <span class="pl-en">&lt;module&gt;</span>
    joinedload(<span class="pl-s"><span class="pl-pds">'</span>children<span class="pl-pds">'</span></span>).undefer(<span class="pl-s"><span class="pl-pds">'</span>deep_children_count<span class="pl-pds">'</span></span>)).get(<span class="pl-c1">2</span>)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py"</span>, line <span class="pl-c1">831</span>, in <span class="pl-en">get</span>
    <span class="pl-k">return</span> <span class="pl-c1">self</span>._get_impl(ident, loading.load_on_ident)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py"</span>, line <span class="pl-c1">864</span>, in <span class="pl-en">_get_impl</span>
    <span class="pl-k">return</span> fallback_fn(<span class="pl-c1">self</span>, key)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py"</span>, line <span class="pl-c1">219</span>, in <span class="pl-en">load_on_ident</span>
    <span class="pl-k">return</span> q.one()
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py"</span>, line <span class="pl-c1">2693</span>, in <span class="pl-en">one</span>
    ret <span class="pl-k">=</span> <span class="pl-c1">list</span>(<span class="pl-c1">self</span>)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py"</span>, line <span class="pl-c1">2736</span>, in <span class="pl-en">__iter__</span>
    <span class="pl-k">return</span> <span class="pl-c1">self</span>._execute_and_instances(context)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py"</span>, line <span class="pl-c1">2751</span>, in <span class="pl-en">_execute_and_instances</span>
    result <span class="pl-k">=</span> conn.execute(querycontext.statement, <span class="pl-c1">self</span>._params)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py"</span>, line <span class="pl-c1">914</span>, in <span class="pl-en">execute</span>
    <span class="pl-k">return</span> meth(<span class="pl-c1">self</span>, multiparams, params)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py"</span>, line <span class="pl-c1">323</span>, in <span class="pl-en">_execute_on_connection</span>
    <span class="pl-k">return</span> connection._execute_clauseelement(<span class="pl-c1">self</span>, multiparams, params)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py"</span>, line <span class="pl-c1">1003</span>, in <span class="pl-en">_execute_clauseelement</span>
    inline<span class="pl-k">=</span><span class="pl-c1">len</span>(distilled_params) <span class="pl-k">&gt;</span> <span class="pl-c1">1</span>)
  File <span class="pl-s">"&lt;string&gt;"</span>, line <span class="pl-c1">1</span>, in <span class="pl-en">&lt;lambda&gt;</span>
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py"</span>, line <span class="pl-c1">494</span>, in <span class="pl-en">compile</span>
    <span class="pl-k">return</span> <span class="pl-c1">self</span>._compiler(dialect, <span class="pl-v">bind</span><span class="pl-k">=</span>bind, <span class="pl-k">**</span>kw)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py"</span>, line <span class="pl-c1">500</span>, in <span class="pl-en">_compiler</span>
    <span class="pl-k">return</span> dialect.statement_compiler(dialect, <span class="pl-c1">self</span>, <span class="pl-k">**</span>kw)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">395</span>, in <span class="pl-en">__init__</span>
    Compiled.<span class="pl-c1">__init__</span>(<span class="pl-c1">self</span>, dialect, statement, <span class="pl-k">**</span>kwargs)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">190</span>, in <span class="pl-en">__init__</span>
    <span class="pl-c1">self</span>.string <span class="pl-k">=</span> <span class="pl-c1">self</span>.process(<span class="pl-c1">self</span>.statement, <span class="pl-k">**</span>compile_kwargs)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">213</span>, in <span class="pl-en">process</span>
    <span class="pl-k">return</span> obj._compiler_dispatch(<span class="pl-c1">self</span>, <span class="pl-k">**</span>kwargs)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py"</span>, line <span class="pl-c1">81</span>, in <span class="pl-en">_compiler_dispatch</span>
    <span class="pl-k">return</span> meth(<span class="pl-c1">self</span>, <span class="pl-k">**</span>kw)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">1585</span>, in <span class="pl-en">visit_select</span>
    <span class="pl-k">for</span> name, column <span class="pl-k">in</span> select._columns_plus_names
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">1357</span>, in <span class="pl-en">_label_select_column</span>
    <span class="pl-k">**</span>column_clause_args
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py"</span>, line <span class="pl-c1">93</span>, in <span class="pl-en">_compiler_dispatch</span>
    <span class="pl-k">return</span> meth(<span class="pl-c1">self</span>, <span class="pl-k">**</span>kw)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">615</span>, in <span class="pl-en">visit_label</span>
    <span class="pl-c1">OPERATORS</span>[operators.as_] <span class="pl-k">+</span> \
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py"</span>, line <span class="pl-c1">93</span>, in <span class="pl-en">_compiler_dispatch</span>
    <span class="pl-k">return</span> meth(<span class="pl-c1">self</span>, <span class="pl-k">**</span>kw)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">527</span>, in <span class="pl-en">visit_grouping</span>
    <span class="pl-k">return</span> <span class="pl-s"><span class="pl-pds">"</span>(<span class="pl-pds">"</span></span> <span class="pl-k">+</span> grouping.element._compiler_dispatch(<span class="pl-c1">self</span>, <span class="pl-k">**</span>kwargs) <span class="pl-k">+</span> <span class="pl-s"><span class="pl-pds">"</span>)<span class="pl-pds">"</span></span>
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py"</span>, line <span class="pl-c1">81</span>, in <span class="pl-en">_compiler_dispatch</span>
    <span class="pl-k">return</span> meth(<span class="pl-c1">self</span>, <span class="pl-k">**</span>kw)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">1608</span>, in <span class="pl-en">visit_select</span>
    text, select, inner_columns, froms, byfrom, kwargs)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">1700</span>, in <span class="pl-en">_compose_select_body</span>
    <span class="pl-k">for</span> f <span class="pl-k">in</span> froms])
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py"</span>, line <span class="pl-c1">81</span>, in <span class="pl-en">_compiler_dispatch</span>
    <span class="pl-k">return</span> meth(<span class="pl-c1">self</span>, <span class="pl-k">**</span>kw)
  File <span class="pl-s">"/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py"</span>, line <span class="pl-c1">1194</span>, in <span class="pl-en">visit_cte</span>
    cte_name)
<span class="pl-en">sqlalchemy.exc.CompileError</span>: <span class="pl-s">Multiple, unrelated CTEs found with the same name: u'anon_2'</span></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Henrique Alves (<a href="https://github.com/hcarvalhoalves">@hcarvalhoalves</a>)</strong></p>
<p dir="auto">I'm trying to do something that, in principle, should be simple: inject a expression-based column into an ORM-based Query. I've tried this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="query = db.session.query(MyClass).all()
# Using `now` just as an example, ideally it's something that takes a parameter
# so it can't be replaced by `orm.column_property`
some_expression = func.now() 
selectable = query.selectable
# The following works as expected, injecting the expression as a column
# the AnnotatedSelect object...
selectable.append_column(some_expression.label('some_column'))
# ... but I can't set it back on the query
query.selectable = selectable
query = query.order_by('some_column')
"><pre class="notranslate"><code class="notranslate">query = db.session.query(MyClass).all()
# Using `now` just as an example, ideally it's something that takes a parameter
# so it can't be replaced by `orm.column_property`
some_expression = func.now() 
selectable = query.selectable
# The following works as expected, injecting the expression as a column
# the AnnotatedSelect object...
selectable.append_column(some_expression.label('some_column'))
# ... but I can't set it back on the query
query.selectable = selectable
query = query.order_by('some_column')

</code></pre></div>
<p dir="auto">It fails with <code class="notranslate">AttributeError</code> since <code class="notranslate">query.selectable</code> is read-only. I haven't found another way to inject an expression by reading the docs or the source.</p>
<ul dir="auto">
<li>
<p dir="auto"><code class="notranslate">Query.add_column</code> isn't quite the same thing since you now get a <code class="notranslate">KeyedTuple</code> back, not a <code class="notranslate">MyClass</code> object with the injected attribute.</p>
</li>
<li>
<p dir="auto"><code class="notranslate">orm.column_property</code> is of limited usefulness since you can't pass arguments only known at runtime instead of class-evaluation time.</p>
</li>
</ul>
<p dir="auto">I thought SQLAlchemy made things like this a breeze, but I've spent days without finding an adequate solution. Either I'm missing something, or an API for this is badly needed.</p>