Weird interaction between `subqueryload` and deeply-nested relationships causing exception sqlalchemy.exc.InvalidRequestError: Entity '<AliasedClass at 0x2b74a90; Employee>' has no property 'department'

[Jack Zhou]

The configuration:

class Company(Base):
    __tablename__ = "company"
    id = Column(Integer, primary_key=True)


class Department(Base):
    __tablename__ = "department"
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey(Company.id), nullable=False)
    company = relationship(Company, lazy="subquery")


class Employee(Base):
    __tablename__ = "employee"
    id = Column(Integer, primary_key=True)
    type = Column(Enum("engineer", name="employee_type"), nullable=False)
    __mapper_args__ = {
        "polymorphic_on": type,
        "with_polymorphic": "*"
    }


class Engineer(Employee):
    __tablename__ = "engineer"
    id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
    department_id = Column(Integer, ForeignKey(Department.id), nullable=False)
    department = relationship(Department, lazy="subquery")
    __mapper_args__ = {
        "polymorphic_identity": "engineer"
    }


class Car(Base):
    __tablename__ = "car"
    id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, ForeignKey(Employee.id), nullable=False)
    employee = relationship(Employee, lazy="subquery")

The query:

session.add(Car(employee=Engineer(department=Department(company=Company()))))
session.query(Car).all()

This results in the following exception:

#!

Traceback (most recent call last):
  File "./bug.py", line 71, in <module>
    main()
  File "./bug.py", line 66, in main
    db.query(Car).all()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2293, in all
    return list(self)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 72, in instances
    rows = [process[0](row, None) for row in fetch]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 452, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 305, in populate_state
    populator(state, dict_, row)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1001, in load_scalar_from_subq
    (None,)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 937, in get
    self._load()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 945, in _load
    lambda x: x[1:]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 942, in <genexpr>
    (k, [vv[0] for vv in v])
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 75, in instances
    labels) for row in fetch]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 347, in _instance
    return _instance(row, result)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 452, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 305, in populate_state
    populator(state, dict_, row)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1001, in load_scalar_from_subq
    (None,)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 937, in get
    self._load()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 945, in _load
    lambda x: x[1:]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2401, in __iter__
    context = self._compile_context()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2764, in _compile_context
    entity.setup_context(self, context)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3145, in setup_context
    column_collection=context.primary_columns
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 463, in setup
    strat.setup_query(context, entity, path, loader, adapter, **kwargs)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 735, in setup_query
    parent_alias, effective_entity)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 891, in _apply_joins
    q = q.join(parent_alias, attr, from_joinpoint=True)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1699, in join
    from_joinpoint=from_joinpoint)
  File "<string>", line 2, in _join
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 165, in generate
    fn(self, *args[1:], **kw)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1772, in _join
    descriptor = _entity_descriptor(left_entity, onclause)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 342, in _entity_descriptor
    (description, key)
sqlalchemy.exc.InvalidRequestError: Entity '<AliasedClass at 0x2b74a90; Employee>' has no property 'department'

One of the following things fixes the problem:

1. Removing the Department-Company relationship
2. Change any of the relationships to be lazyload
3. Remove "with_polymorphic": "*" from Employee

[Mike Bayer]

the resolution for this bug will likely have to involve that the subquery load for the Engineer.department does not occur. with_polymorphic='*' is already very intricate and just getting it to skip over this attribute will probably be difficult enough.

[Mike Bayer]

this one is a real butt kicker....well here's how to simulate the same thing using options:

wp = with_polymorphic(Employee, "*")
for c in s.query(Car).options(subqueryload(Car.employee.of_type(wp)).subqueryload(wp.Engineer.department).subqueryload("company")):
    print c.employee.department.company

it behaves the same way so that's good

[Mike Bayer]

OK well guess I got lucky, we need to make use of some more specific info and all that's needed is this!

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 3ebadd6..c8946a0 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -885,7 +885,7 @@ class SubqueryLoader(AbstractRelationshipLoader):
                     attr = getattr(parent_alias, key).\
                                     of_type(effective_entity)
                 else:
-                    attr = key
+                    attr = getattr(mapper.entity, key)

             if second_to_last:
                 q = q.join(parent_alias, attr, from_joinpoint=True)

SQLAlchemy amazes me that it can figure out the queries for this one.

[Jack Zhou]

Great, works for me! I love one line fixes.

[Mike Bayer]

• Fixed bug in subquery eager loading where a long chain of eager loads across a polymorphic-subclass boundary in conjunction with polymorphic loading would fail to locate the subclass-link in the chain, erroring out with a missing property name on an :class:.AliasedClass. fixes #3055

→ <<cset 95b10c4e8e59>>

[Mike Bayer]

• Fixed bug in subquery eager loading where a long chain of eager loads across a polymorphic-subclass boundary in conjunction with polymorphic loading would fail to locate the subclass-link in the chain, erroring out with a missing property name on an :class:.AliasedClass. fixes #3055

→ <<cset 7dc497be982e>>

[Mike Bayer]

• Fixed bug in subquery eager loading where a long chain of eager loads across a polymorphic-subclass boundary in conjunction with polymorphic loading would fail to locate the subclass-link in the chain, erroring out with a missing property name on an :class:.AliasedClass. fixes #3055
• adjust the test from 1.0/0.9 to not use chained eager load style

→ <<cset 5cf8dbeb5efd>>

[Mike Bayer]

all the way back to 0.8, thanks for the good test case