Issue 1:
Migrated issue, originally created by Lukas Siemon (@lukas-gitl)

The test case below generates the following query:

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

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?

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".

I've tested the issue with

SQLAlchemy==1.0.8

Flask==0.10.1

Flask-SQLAlchemy==2.0

Test case:

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())

Issue 2:
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker,relationship,contains_eager
from sqlalchemy.ext.declarative import declarative_base

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

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

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

class School(Base):
__tablename__="schools"
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))

As there's only one row returned in the subquery , the length of childs should be 1.But the script prints 2. And if

sa.func.json_object_agg(Child.name,Child.id).label('names')

is removed and the subquery is replaced by

sub=session.query(sa.func.max(Child.id).label('id')).subquery(with_labels=True)

Then the script print 1 normally.

This anomaly can also be reproduced on mysql using json_objectagg function instead.

data in these three tables are

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)