[Sqlalchemy-tickets] Issue #3773: subquery eager load from of_type() to plain mapper chains joins incorrectly (zzzeek/sqlalchemy)

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

New issue 3773: subquery eager load from of_type() to plain mapper chains joins incorrectly
https://bitbucket.org/zzzeek/sqlalchemy/issues/3773/subquery-eager-load-from-of_type-to-plain

Michael Bayer:

The SQL at the end gives us a "FROM <join of things>, <join of things>".

```
#!python

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, sessionmaker, subqueryload,
                            with_polymorphic, joinedload)
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.schema import ForeignKey

Base = declarative_base()


class B(Base):
    __tablename__ = 't_b'

    id = Column(Integer, primary_key=True)


class C(Base):
    __tablename__ = 't_c'

    type = Column(String(2))
    __mapper_args__ = {
        'polymorphic_identity': 'c',
        'polymorphic_on': type
    }

    id = Column(Integer, primary_key=True)

    # Relationship to B
    b_id = Column(Integer, ForeignKey('t_b.id'))
    b = relationship('B', backref='cs')


class C2(C):
    __tablename__ = 't_c2'

    __mapper_args__ = {
        'polymorphic_identity': 'c2',
    }

    id = Column(Integer, ForeignKey('t_c.id'), primary_key=True)


class D(Base):
    __tablename__ = 't_d'

    id = Column(Integer, primary_key=True)

    # Relationship to B
    c_id = Column(Integer, ForeignKey('t_c.id'))
    c = relationship('C', backref='ds')

engine = create_engine('sqlite://', echo=True)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

for i in xrange(2):
    b = B()
    session.add(b)

    c = C2(b=b)
    session.add(c)

    d = D(c=c)
    session.add(d)

session.commit()

c_c2 = with_polymorphic(C, [C2], flat=True)

r = session.query(
    B
).options(
    subqueryload(
        B.cs.of_type(c_c2)
    ).subqueryload(
        c_c2.ds# .of_type(D)
    )
).all()


```

the patch is:


```
#!diff

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 8260732..1d0058c 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -968,7 +968,7 @@ class SubqueryLoader(AbstractRelationshipLoader):
                 if last and effective_entity is not self.mapper:
                     attr = attr.of_type(effective_entity)
             else:
-                if last and effective_entity is not self.mapper:
+                if last:
                     attr = getattr(parent_alias, key).\
                         of_type(effective_entity)
                 else:

```

the code with effective_entity was introduced in 3dd536ac06808adcf9c10707dbf2ebb6e3842be7, the test for it is test_of_type -> SubclassRelationshipTest.test_twolevel_subqueryload_wsubclass introduced in 4a4afca9595c9b4b10e6557c6ee819ae386c477a.