Eager loading does incorrect join on self-referential polymorphic relationship

[sqlalchemy-bot]

Migrated issue, originally created by Jack Zhou (@univerio)

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

you're not doing anything wrong at all, other than combining all the worst nightmare features of the ORM for me to debug at the same time :)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this bug was introduced in 1.0.x but I'm trying to stop putting out 1.0.x releases for most things, this would be targeted at 1.1.6. hope that's OK.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: orm

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.1.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

oh, it was introduced in 0.9.7. so an old bug

[sqlalchemy-bot]

Jack Zhou (@univerio) wrote:

Thanks for the quick response! It's completely fine with me if it goes out in 1.1.6. I can even use master in the meantime. I don't imagine too many people are hitting this issue if it went unnoticed since 0.9.7, heh :)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Dont set _set_select_from() for alias object

Fixed bug first introduced in 0.9.7 as a result of 🎫3106
which would cause an incorrect query in some forms of multi-level
subqueryload against aliased entities, with an unnecessary extra
FROM entity in the innermost subquery.

Fixes: #3893

Change-Id: Ic4003c2c1c0206bd22a098fd497a7375c2758305

→ 4ae02f4

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed