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)
-
repo owner -
repo owner 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.
-
repo owner oh, it was introduced in 0.9.7. so an old bug
-
reporter 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 :)
-
repo owner - changed status to resolved
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:
#3893Change-Id: Ic4003c2c1c0206bd22a098fd497a7375c2758305
→ <<cset 4ae02f46e944>>
- Log in to comment
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 :)