Issue 1:
Migrated issue, originally created by Whitney Young (@wbyoung)

Given the following relations:

Company has many employees and a parent company

Employee has an employer

And the following instances:

Apple w/ employees Tim Cook & Jony Ive

Beats w/ employees Dr. Dre & Jimmy Iovine

The following query does not load everything:

employees = session.query(Employee).options(
joinedload('employer').joinedload('parent').subqueryload('employees')
).filter().all()

It appears that it finds all employees and parents properly, but since all the companies get loaded into the session via the joined load, it seems to stop processing at this point and doesn't actually perform the subqueryload of employees. This example may seem contrived since it's circling back around through the relationships, but it's a simplified version of something that w/ filters applied is a very real query in our system.

Below is some code to run to show the problem. Just change the connection string to be able to connect to your database, the code will populate a few records for you.

import logging
from contextlib import contextmanager
from sqlalchemy import (
Column,
ForeignKey,
Integer,
MetaData,
String,
Table,
create_engine,
inspect,
)
from sqlalchemy.orm import (
mapper,
relationship,
sessionmaker,
subqueryload,
joinedload,
)

class Model(object):
def __init__(self, **kwargs):
for key, value in kwargs.items():
setattr(self, key, value)
class Company(Model): pass
class Employee(Model): pass

engine = create_engine(
'mysql+pymysql://root:password@localhost/test?charset=utf8mb4',
convert_unicode=True,
)
metadata = MetaData(bind=engine)
Session = sessionmaker(bind=engine)

company_table = Table(
'companies', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('name', String(255)),
Column('parent_id', Integer, ForeignKey('companies.id')),
)
employee_table = Table(
'employees', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('name', String(255)),
Column('employer_id', Integer, ForeignKey('companies.id')),
)
company_mapper = mapper(Company, company_table, properties={
'parent': relationship(
Company,
uselist=False,
remote_side=[company_table.columns.id],
),
'employees': relationship(
Employee,
uselist=True,
back_populates='employer',
),
})

employee_mapper = mapper(Employee, employee_table, properties={
'employer': relationship(
Company,
uselist=False,
back_populates='employees',
)
})

connection = engine.connect()
metadata.create_all(connection)
connection.close()

@contextmanager
def session_manager():
session = Session()
try: yield session; session.commit()
except: session.rollback(); raise
finally: session.close()

def _ensure_loaded(obj, key_path):
components = key_path.split('.')
for i, key in enumerate(components):
if key in inspect(obj).unloaded:
raise AttributeError('Attribute {} not loaded during query'.format(
'.'.join(components[:i+1])
))
obj = getattr(obj, key)

if obj is None:
break

models = [
Company(id=1, name='Apple'),
Company(id=2, name='Beats', parent_id=1),
Employee(id=1, name='Tim Cook', employer_id=1),
Employee(id=2, name='Jony Ive', employer_id=1),
Employee(id=3, name='Dr. Dre', employer_id=2),
Employee(id=4, name='Jimmy Iovine', employer_id=2),
]

for model in models:
with session_manager() as session:
try:
session.add(model)
session.commit()
except Exception as e: session.close()

logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

with session_manager() as session:
employees = session.query(Employee).options(
joinedload('employer').joinedload('parent').subqueryload('employees')
).filter(Employee.id == 3).all()

for employee in employees:
_ensure_loaded(employee, 'employer.parent.employees')

# this block of code fails presumably because the parent employer is loaded into the session during
# the first joinedload of the employer which doesn't trigger the subqueryload for parent.employees
# for the company that actually does have a parent company.
with session_manager() as session:
employees = session.query(Employee).options(
joinedload('employer').joinedload('parent').subqueryload('employees')
).filter().all()

for employee in employees:
_ensure_loaded(employee, 'employer.parent.employees')

Issue 2:
Migrated issue, originally created by Kent Rakip (@krakip)

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:

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