[Sqlalchemy-tickets] Issue #3854: subqueryload is not executed when a member of the same collection is loaded via joinedload (zzzeek/sqlalchemy)

[Kent R. <iss...@bi...>]

New issue 3854: subqueryload is not executed when a member of the same collection is loaded via joinedload
https://bitbucket.org/zzzeek/sqlalchemy/issues/3854/subqueryload-is-not-executed-when-a-member

Kent Rakip:

When a `joinedload` path causes any entity in a one-to-many relationship to be eagerly loaded, any `subqueryload` path for the same relationship will not be emitted, even if it might return additional results.

Here is a minimal repro case:


```
#!python

from sqlalchemy import Column, ForeignKey, Integer, create_engine, inspect
from sqlalchemy.orm import relationship, sessionmaker, joinedload
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Thing(Base):
    __tablename__ = 'Thing'

    id = Column(Integer, primary_key=True, autoincrement=True)
    another_thing_id = Column(ForeignKey('AnotherThing.id'))
    another_thing = relationship('AnotherThing')

    yet_another_thing_id = Column(ForeignKey('YetAnotherThing.id'))
    yet_another_thing = relationship('YetAnotherThing')


class AnotherThing(Base):
    __tablename__ = 'AnotherThing'

    id = Column(Integer, primary_key=True, autoincrement=True)


class YetAnotherThing(Base):
    __tablename__ = 'YetAnotherThing'

    id = Column(Integer, primary_key=True, autoincrement=True)

    another_thing_id = Column(ForeignKey('AnotherThing.id'))
    another_thing = relationship('AnotherThing', backref='yet_another_things')
    last_thing_id = Column(ForeignKey('LastThing.id'))
    last_thing = relationship('LastThing')


class LastThing(Base):
    __tablename__ = 'LastThing'

    id = Column(Integer, primary_key=True, autoincrement=True)


engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session_factory = sessionmaker(bind=engine)

session = session_factory()

thing = Thing()
thing.another_thing = AnotherThing()
thing.another_thing.yet_another_things = [YetAnotherThing(), YetAnotherThing()]
thing.yet_another_thing = thing.another_thing.yet_another_things[0]
thing.another_thing.yet_another_things[0].last_thing = LastThing()
thing.another_thing.yet_another_things[1].last_thing = LastThing()

session.add(thing)
session.commit()

session2 = session_factory()

t = (
    session2.query(Thing)
        .options(
            joinedload(Thing.another_thing)
                .subqueryload(AnotherThing.yet_another_things)
                .joinedload(YetAnotherThing.last_thing),
            joinedload(Thing.yet_another_thing)
                .joinedload(YetAnotherThing.another_thing),
        )
        .get(1)
)

# Should print 'False'; actually prints 'True' on 1.1.4
print('yet_another_things' in inspect(t.another_thing).unloaded)
```

The query that should load all of the values for `AnotherThing.yet_another_things` is never emitted, and as a result `last_thing` is not loaded for any of the `YetAnotherThing`s either, since that occurs later on the same path. Commenting out the second `joinedload` path causes the `subqueryload` query to be emitted correctly.

This seems to be a regression in 1.1; I'm unable to reproduce it on 1.0.16, although it is possible that the behavior is nondeterministic and I never got lucky.