[Sqlalchemy-tickets] Issue #4011: joined subclass to 2 level subquery load fails on join (zzzeek/sqlalchemy)

[Michael B. <iss...@bi...>]

New issue 4011: joined subclass to 2 level subquery load fails on join
https://bitbucket.org/zzzeek/sqlalchemy/issues/4011/joined-subclass-to-2-level-subquery-load

Michael Bayer:

Currently cannot be tested on master due to #3963.

under 1.1, the third query is losing join context

```
#!sql


SELECT dept.id AS dept_id, owner_1.dept_id AS owner_1_dept_id 
FROM (SELECT DISTINCT milestone.owner_id AS milestone_owner_id 
FROM milestone, sprint 
WHERE sprint.id IN (?)) AS anon_1 JOIN owner AS owner_1 ON owner_1.id = anon_1.milestone_owner_id JOIN dept ON dept.id = owner_1.dept_id ORDER BY owner_1.dept_id



```


```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Milestone(Base):
    __tablename__ = 'milestone'
    id = Column(Integer, primary_key=True)

    milestone_type = Column(String(64), nullable=False)
    owner_id = Column(
        Integer,
        ForeignKey('owner.id', name='milestone_owner_id_owner_id_fkey'))

    owner = relationship('Owner', back_populates='milestone')

    __mapper_args__ = {
        'polymorphic_on': milestone_type,
    }


class Sprint(Milestone):
    __tablename__ = 'sprint'
    id = Column(
        Integer,
        ForeignKey('milestone.id', name='sprint_id_milestone_id_fkey'),
        primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity': 'sprint'
    }


class Owner(Base):
    __tablename__ = 'owner'
    id = Column(Integer, primary_key=True)

    dept_id = Column(
        Integer,
        ForeignKey('dept.id', name='owner_dept_id_dept_id_fkey'))
    dept = relationship('Dept', back_populates='owner')
    milestone = relationship('Milestone', back_populates='owner')


class Dept(Base):
    __tablename__ = 'dept'
    id = Column(Integer, primary_key=True)
    owner = relationship('Owner', back_populates='dept')


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

d = Dept(id=1)
o = Owner(id=1)
o.dept = d
s = Sprint(id=1)
s.owner = o
sess.add(o)
sess.commit()

sess.query(Sprint).filter(Sprint.id.in_([1])).options(
    subqueryload(Sprint.owner).subqueryload(Owner.dept)).all()


```