Eager loading does incorrect join on self-referential polymorphic relationship

For the following models:

class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    type = Column(String)

    foo_id = Column(Integer, ForeignKey("foo.id"))
    foo = relationship(lambda: Foo, foreign_keys=foo_id, remote_side=id)

    __mapper_args__ = {
        "polymorphic_on": type,
        "polymorphic_identity": "foo",
    }


class Bar(Foo):
    __mapper_args__ = {
        "polymorphic_identity": "bar",
    }

And the following app code:

I = with_polymorphic(Foo, "*", aliased=True)
attr1 = Foo.foo.of_type(I)
attr2 = I.foo
session.query(Foo).options(subqueryload(attr1), subqueryload(attr1, attr2)).all()

The main query and the first subquery are correct, but the third contains an extra cross join:

SELECT foo.id AS foo_id_1, foo.type AS foo_type, foo.foo_id AS foo_foo_id, foo_1.foo_id AS foo_1_foo_id 
FROM (SELECT DISTINCT foo.foo_id AS foo_foo_id 
FROM foo, foo AS foo_1) AS anon_1 JOIN foo AS foo_1 ON foo_1.id = anon_1.foo_foo_id JOIN foo ON foo.id = foo_1.foo_id ORDER BY foo_1.foo_id

This used to work on 0.9.4:

SELECT foo.id AS foo_id_1, foo.type AS foo_type, foo.foo_id AS foo_foo_id, foo_1.foo_id AS foo_1_foo_id 
FROM (SELECT DISTINCT foo.foo_id AS foo_foo_id 
FROM foo) AS anon_1 JOIN foo AS foo_1 ON foo_1.id = anon_1.foo_foo_id JOIN foo ON foo.id = foo_1.foo_id ORDER BY foo_1.foo_id

But maybe the incantation I'm using is wrong.

Comments (5)