Issue 1:
Migrated issue, originally created by Adrian (@thiefmaster)

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

Base = declarative_base()

class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
creator_id = Column(
Integer,
ForeignKey('users.id'),
index=True,
nullable=True
)
creator = relationship(
'User',
lazy=True,
backref=backref(
'created_items',
lazy=True
)
)
acl_entries = relationship(
'ACL',
lazy=True,
backref=backref(
'item',
lazy=True
)
)

class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
extra_id = Column(
Integer,
ForeignKey('extra.id'),
index=True,
nullable=False
)
extra = relationship(
'Extra',
lazy=True,
backref=backref(
'user',
lazy=True,
uselist=False
)
)

class Extra(Base):
__tablename__ = 'extra'
id = Column(Integer, primary_key=True)

class ACL(Base):
__tablename__ = 'acl_entries'
id = Column(Integer, primary_key=True)
item_id = Column(
Integer,
ForeignKey('items.id'),
index=True,
nullable=True
)
user_id = Column(
Integer,
ForeignKey('users.id'),
index=True,
nullable=True
)
user = relationship(
'User',
lazy=True,
backref=backref(
'acl_entries',
lazy=True
)
)

e = create_engine('sqlite:///:memory:', echo=True)
# e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)

item = Item()
user = User(extra=Extra())
item.creator = user
item.acl_entries = [ACL(user=user)]
s.add(item)
s.flush()
s.expire_all()

print

query = (s.query(Item)
.options(joinedload('acl_entries').joinedload('user').noload('extra'),
joinedload('creator').joinedload('extra')))
for item in query:
print 'Item', item
print 'Item.creator', item.creator
print 'Item.creator.extra', item.creator.extra
print 'Item.acl_entries'
for ae in item.acl_entries:
print ae, ae.user, ae.user.extra

Output:

Item <__main__.Item object at 0x7f2df31d5450>
Item.creator <__main__.User object at 0x7f2df31f9050>
Item.creator.extra None
Item.acl_entries
<__main__.ACL object at 0x7f2df316ecd0> <__main__.User object at 0x7f2df31f9050> None

I would expect Item.creator.extra to be available. This example is based on a real application where I don't care about certain User relationships when loading the ACL for an object (so i noload them), but at the same time I do need the data from the relationship for the "creator" of the object (which is a relationship between Item and User).

The data is actually queried, but apparently discarded somewhere within SQLAlchemy:

SELECT items.id AS items_id,
items.creator_id AS items_creator_id,
extra_1.id AS extra_1_id,
users_1.id AS users_1_id,
users_1.extra_id AS users_1_extra_id,
users_2.id AS users_2_id,
users_2.extra_id AS users_2_extra_id,
acl_entries_1.id AS acl_entries_1_id,
acl_entries_1.item_id AS acl_entries_1_item_id,
acl_entries_1.user_id AS acl_entries_1_user_id
FROM items
LEFT OUTER JOIN users AS users_1 ON users_1.id = items.creator_id
LEFT OUTER JOIN extra AS extra_1 ON extra_1.id = users_1.extra_id
LEFT OUTER JOIN acl_entries AS acl_entries_1 ON items.id = acl_entries_1.item_id
LEFT OUTER JOIN users AS users_2 ON users_2.id = acl_entries_1.user_id

Issue 2:
Migrated issue, originally created by Michael Bayer (@zzzeek)

test:

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

Base = declarative_base()

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
b_id = Column(ForeignKey('b.id'))
c_id = Column(ForeignKey('c.id'))

b = relationship("B")
c = relationship("C")

class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
c_id = Column(ForeignKey('c.id'))

c = relationship("C")

class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
d_id = Column(ForeignKey('d.id'))
d = relationship("D")

class D(Base):
__tablename__ = 'd'
id = Column(Integer, primary_key=True)

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

s = Session(e)

c = C(d=D())

s.add(
A(b=B(c=c), c=c)
)
s.commit()

c_alias_1 = aliased(C)
c_alias_2 = aliased(C)

q = s.query(A)
q = q.join(A.b).join(c_alias_1, B.c).join(c_alias_1.d)
q = q.options(contains_eager(A.b).contains_eager(B.c, alias=c_alias_1).contains_eager(C.d))
q = q.join(c_alias_2, A.c)
q = q.options(contains_eager(A.c, alias=c_alias_2))

a1 = q.all()[0]
assert 'd' in a1.c.__dict__

this test relies on the order in which we load attributes, and for some reason PYTHONHASHSEED isn't doing the job in all cases, so test with this patch:

diff --git a/lib/sqlalchemy/orm/loading.py b/lib/sqlalchemy/orm/loading.py
index 50afaf6..8de66de 100644
--- a/lib/sqlalchemy/orm/loading.py
+++ b/lib/sqlalchemy/orm/loading.py
@@ -295,6 +295,13 @@ def _instance_processor(
quick_populators = path.get(
context.attributes, "memoized_setups", _none_set)

+ import random
+ props = list(props)
+ random.shuffle(props)
+
+ print(
+ "PROPS!!!!",[p.key for p in props]
+ )
for prop in props:
if prop in quick_populators:

there's no way to force this loader to happen in all cases because ultimately when the C object is already there, the "isnew" flag is not set and we naturally hit the "existing" loader. However, the "existing" loader can do a check here and populate the key, and we are checking anyway:

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 78e9293..dff51b9 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -1559,13 +1559,15 @@ class JoinedLoader(AbstractRelationshipLoader):
# call _instance on the row, even though the object has
# been created, so that we further descend into properties
existing = _instance(row)
- if existing is not None \
- and key in dict_ \
- and existing is not dict_[key]:
- util.warn(
- "Multiple rows returned with "
- "uselist=False for eagerly-loaded attribute '%s' "
- % self)
+ if existing is not None:
+ if key in dict_:
+ if existing is not dict_[key]:
+ util.warn(
+ "Multiple rows returned with "
+ "uselist=False for eagerly-loaded attribute '%s' "
+ % self)
+ else:
+ dict_[key] = existing

def load_scalar_from_joined_exec(state, dict_, row):
_instance(row)

# this is an inlined path just for column-based attributes.

would need to do the deep thinking here to see if this is right.