[Sqlalchemy-tickets] Issue #3055: Weird interaction between `subqueryload` and deeply-nested relationships causing exception sqlalchemy.exc.InvalidRequestError: Entity '<AliasedClass at 0x2b74a90; Employee>' has no property 'department' (zzzeek/sqlalchemy)

[Jack Z. <iss...@bi...>]

New issue 3055: Weird interaction between `subqueryload` and deeply-nested relationships causing exception sqlalchemy.exc.InvalidRequestError: Entity '<AliasedClass at 0x2b74a90; Employee>' has no property 'department'
https://bitbucket.org/zzzeek/sqlalchemy/issue/3055/weird-interaction-between-subqueryload-and

Jack Zhou:

The configuration:

```
#!python

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:


```
#!python

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`