<p dir="auto"><strong>Migrated issue, originally created by Lukas Siemon (<a href="https://github.com/lukas-gitl">@lukas-gitl</a>)</strong></p>
<p dir="auto">The test case below generates the following query:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT
   label_alias.id,
   label_alias.name,
   anon_1.venue_id,
   anon_1.venue_name   
FROM
   label AS label_alias,  -- this should come from the inner query
   (SELECT
      venue.id AS venue_id,
      venue.name AS venue_name   
   FROM
      venue 
   JOIN
      venue_to_label AS venue_to_label_1 
         ON venue.id = venue_to_label_1.venue_id 
   JOIN
      label AS label_alias 
         ON label_alias.id = venue_to_label_1.label_id   
   WHERE
      label_alias.name IN (
         %(
            name_1
         )s
      )) AS anon_1"><pre class="notranslate"><code class="notranslate">SELECT
   label_alias.id,
   label_alias.name,
   anon_1.venue_id,
   anon_1.venue_name   
FROM
   label AS label_alias,  -- this should come from the inner query
   (SELECT
      venue.id AS venue_id,
      venue.name AS venue_name   
   FROM
      venue 
   JOIN
      venue_to_label AS venue_to_label_1 
         ON venue.id = venue_to_label_1.venue_id 
   JOIN
      label AS label_alias 
         ON label_alias.id = venue_to_label_1.label_id   
   WHERE
      label_alias.name IN (
         %(
            name_1
         )s
      )) AS anon_1
</code></pre></div>
<p dir="auto">However, based on the alchemy query, I'd expect the label_alias to come from the inner sql query instead of from the outer. Is there an easy way to resolve this?</p>
<p dir="auto">To give some background: We're using this strategy generically with joinedload instead of contains_eager to serialize all our queries to json. It works really well, however now the requirement came up to only return "certain joins from the joinedload".</p>
<p dir="auto">I've tested the issue with<br>
SQLAlchemy==1.0.8<br>
Flask==0.10.1<br>
Flask-SQLAlchemy==2.0</p>
<p dir="auto">Test case:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import unittest
from sqlalchemy import (
    Table, Column, Integer, ForeignKey, String, func)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, aliased, contains_eager)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


# -- create all the database models
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (&quot;sqlite://&quot;)
db = SQLAlchemy(app)

Base = declarative_base()

venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))


class Venue(db.Model):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))
    labels = relationship(Label, secondary=venue_to_label)

db.drop_all()
db.create_all()


class TestContainsEager(unittest.TestCase):

    def test_contains_eager(self):
        query = Venue.query
        # filtered join
        label_alias = aliased(Label, name=&quot;label_alias&quot;)
        query = query.join(label_alias, Venue.labels)
        query = query.filter(label_alias.name.in_([&quot;label1&quot;]))

        # together with a windowing function this allows to correctly apply
        # limit and order to the query, needed since labels is a *-to-many join
        query = query.from_self()

        # define to load from the inner query
        query = query.options(
            contains_eager(
                'labels', alias=label_alias
            ).load_only('id', 'name')
        )

        # contains_eager does not pick up the alias from the inner query
        import sqlalchemy.dialects.postgresql as postgresql
        print query.statement.compile(dialect=postgresql.dialect())
"><pre class="notranslate"><code class="notranslate">import unittest
from sqlalchemy import (
    Table, Column, Integer, ForeignKey, String, func)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, aliased, contains_eager)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


# -- create all the database models
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
db = SQLAlchemy(app)

Base = declarative_base()

venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))


class Venue(db.Model):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))
    labels = relationship(Label, secondary=venue_to_label)

db.drop_all()
db.create_all()


class TestContainsEager(unittest.TestCase):

    def test_contains_eager(self):
        query = Venue.query
        # filtered join
        label_alias = aliased(Label, name="label_alias")
        query = query.join(label_alias, Venue.labels)
        query = query.filter(label_alias.name.in_(["label1"]))

        # together with a windowing function this allows to correctly apply
        # limit and order to the query, needed since labels is a *-to-many join
        query = query.from_self()

        # define to load from the inner query
        query = query.options(
            contains_eager(
                'labels', alias=label_alias
            ).load_only('id', 'name')
        )

        # contains_eager does not pick up the alias from the inner query
        import sqlalchemy.dialects.postgresql as postgresql
        print query.statement.compile(dialect=postgresql.dialect())

</code></pre></div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker,relationship,contains_eager
from sqlalchemy.ext.declarative import declarative_base

engine=sa.create_engine(&quot;postgresql:///postgres&quot;)
Session=sessionmaker(bind=engine)
Base=declarative_base()

class ChildSchool(Base):
    __tablename__=&quot;child_school_associations&quot;
    child_id=sa.Column(sa.Integer,sa.ForeignKey(&quot;child.id&quot;),primary_key=True)
    school_id=sa.Column(sa.Integer,sa.ForeignKey(&quot;schools.id&quot;),primary_key=True)

class Child(Base):
    __tablename__=&quot;child&quot;
    id=sa.Column(sa.Integer,primary_key=True)
    name=sa.Column(sa.String)
    schools=relationship(&quot;School&quot;,secondary=ChildSchool.__table__,backref=&quot;children&quot;)

class School(Base):
    __tablename__=&quot;schools&quot;
    id=sa.Column(sa.Integer,primary_key=True)
    name=sa.Column(sa.String)

session=Session()
sub=session.query(sa.func.json_object_agg(Child.name,Child.id).label('names'),sa.func.max(Child.id).label('id')).subquery(with_labels=True)

childs=session.query(sub).join(Child,sub.c.id==Child.id).join(Child.schools).add_entity(Child).options(contains_eager(Child.schools)).all()
print(len(childs))"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">as</span> <span class="pl-s1">sa</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-s1">sessionmaker</span>,<span class="pl-s1">relationship</span>,<span class="pl-s1">contains_eager</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-s1">engine</span><span class="pl-c1">=</span><span class="pl-s1">sa</span>.<span class="pl-en">create_engine</span>(<span class="pl-s">"postgresql:///postgres"</span>)
<span class="pl-v">Session</span><span class="pl-c1">=</span><span class="pl-en">sessionmaker</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">engine</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">ChildSchool</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span><span class="pl-c1">=</span><span class="pl-s">"child_school_associations"</span>
    <span class="pl-s1">child_id</span><span class="pl-c1">=</span><span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>,<span class="pl-s1">sa</span>.<span class="pl-v">ForeignKey</span>(<span class="pl-s">"child.id"</span>),<span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">school_id</span><span class="pl-c1">=</span><span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>,<span class="pl-s1">sa</span>.<span class="pl-v">ForeignKey</span>(<span class="pl-s">"schools.id"</span>),<span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-k">class</span> <span class="pl-v">Child</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span><span class="pl-c1">=</span><span class="pl-s">"child"</span>
    <span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</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">name</span><span class="pl-c1">=</span><span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">String</span>)
    <span class="pl-s1">schools</span><span class="pl-c1">=</span><span class="pl-en">relationship</span>(<span class="pl-s">"School"</span>,<span class="pl-s1">secondary</span><span class="pl-c1">=</span><span class="pl-v">ChildSchool</span>.<span class="pl-s1">__table__</span>,<span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-s">"children"</span>)

<span class="pl-k">class</span> <span class="pl-v">School</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span><span class="pl-c1">=</span><span class="pl-s">"schools"</span>
    <span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</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">name</span><span class="pl-c1">=</span><span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">String</span>)

<span class="pl-s1">session</span><span class="pl-c1">=</span><span class="pl-v">Session</span>()
<span class="pl-s1">sub</span><span class="pl-c1">=</span><span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-s1">sa</span>.<span class="pl-s1">func</span>.<span class="pl-en">json_object_agg</span>(<span class="pl-v">Child</span>.<span class="pl-s1">name</span>,<span class="pl-v">Child</span>.<span class="pl-s1">id</span>).<span class="pl-en">label</span>(<span class="pl-s">'names'</span>),<span class="pl-s1">sa</span>.<span class="pl-s1">func</span>.<span class="pl-en">max</span>(<span class="pl-v">Child</span>.<span class="pl-s1">id</span>).<span class="pl-en">label</span>(<span class="pl-s">'id'</span>)).<span class="pl-en">subquery</span>(<span class="pl-s1">with_labels</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-s1">childs</span><span class="pl-c1">=</span><span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-s1">sub</span>).<span class="pl-en">join</span>(<span class="pl-v">Child</span>,<span class="pl-s1">sub</span>.<span class="pl-s1">c</span>.<span class="pl-s1">id</span><span class="pl-c1">==</span><span class="pl-v">Child</span>.<span class="pl-s1">id</span>).<span class="pl-en">join</span>(<span class="pl-v">Child</span>.<span class="pl-s1">schools</span>).<span class="pl-en">add_entity</span>(<span class="pl-v">Child</span>).<span class="pl-en">options</span>(<span class="pl-en">contains_eager</span>(<span class="pl-v">Child</span>.<span class="pl-s1">schools</span>)).<span class="pl-en">all</span>()
<span class="pl-en">print</span>(<span class="pl-en">len</span>(<span class="pl-s1">childs</span>))</pre></div>
<p dir="auto">As there's only one row returned in the subquery , the length of childs should be 1.But the script prints 2. And if</p>
<blockquote>
<p dir="auto">sa.func.json_object_agg(Child.name,Child.id).label('names')</p>
</blockquote>
<p dir="auto">is removed and the subquery is replaced by</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="sub=session.query(sa.func.max(Child.id).label('id')).subquery(with_labels=True)"><pre class="notranslate"><span class="pl-s1">sub</span><span class="pl-c1">=</span><span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-s1">sa</span>.<span class="pl-s1">func</span>.<span class="pl-en">max</span>(<span class="pl-v">Child</span>.<span class="pl-s1">id</span>).<span class="pl-en">label</span>(<span class="pl-s">'id'</span>)).<span class="pl-en">subquery</span>(<span class="pl-s1">with_labels</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)</pre></div>
<p dir="auto">Then the script print 1 normally.<br>
This anomaly can also be reproduced on mysql using json_objectagg function instead.</p>
<p dir="auto">data in these three tables are</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="postgres=# select id ,name from child;
 id |     name      
----+---------------
  1 | Ben Lindley
  2 | Kate Green
  5 | Billy Green
  3 | Cathy McMaine
(4 rows)
postgres=# select id,name from schools;
 id |             name              
----+-------------------------------
  1 | new_york_municipal_elementary
  2 | los_angeles_riverside_middle
(2 rows)
postgres=# select child_id,school_id from child_school_associations;
 child_id | school_id 
----------+-----------
        1 |         1
        1 |         2
        2 |         1
        2 |         2
        3 |         1
        3 |         2
        5 |         1
        5 |         2
(8 rows)
"><pre class="notranslate">postgres<span class="pl-k">=</span><span class="pl-c"><span class="pl-c">#</span> select id ,name from child;</span>
 id |     name      
<span class="pl-c"><span class="pl-c">--</span>--+---------------</span>
  <span class="pl-c1">1</span> | Ben Lindley
  <span class="pl-c1">2</span> | Kate Green
  <span class="pl-c1">5</span> | Billy Green
  <span class="pl-c1">3</span> | Cathy McMaine
(<span class="pl-c1">4</span> rows)
postgres<span class="pl-k">=</span><span class="pl-c"><span class="pl-c">#</span> select id,name from schools;</span>
 id |             name              
<span class="pl-c"><span class="pl-c">--</span>--+-------------------------------</span>
  <span class="pl-c1">1</span> | new_york_municipal_elementary
  <span class="pl-c1">2</span> | los_angeles_riverside_middle
(<span class="pl-c1">2</span> rows)
postgres<span class="pl-k">=</span><span class="pl-c"><span class="pl-c">#</span> select child_id,school_id from child_school_associations;</span>
 child_id | school_id 
<span class="pl-c"><span class="pl-c">--</span>--------+-----------</span>
        <span class="pl-c1">1</span> |         <span class="pl-c1">1</span>
        <span class="pl-c1">1</span> |         <span class="pl-c1">2</span>
        <span class="pl-c1">2</span> |         <span class="pl-c1">1</span>
        <span class="pl-c1">2</span> |         <span class="pl-c1">2</span>
        <span class="pl-c1">3</span> |         <span class="pl-c1">1</span>
        <span class="pl-c1">3</span> |         <span class="pl-c1">2</span>
        <span class="pl-c1">5</span> |         <span class="pl-c1">1</span>
        <span class="pl-c1">5</span> |         <span class="pl-c1">2</span>
(<span class="pl-c1">8</span> rows)
</pre></div>