joined eager load innerjoin=True not accommodated by with_polymorphic / joined inh

[sqlalchemy-bot]

Migrated issue, originally created by Julien Cigar

Please see the test case, the error is pretty clear I think :)

---

Attachments: pol.py

[sqlalchemy-bot]

Julien Cigar wrote:

the problem is that SQLAlchemy generates:

FROM content 
LEFT OUTER JOIN document ON content.id = document.content_id 
LEFT OUTER JOIN event ON content.id = event.content_id 
JOIN country AS country_1 ON country_1.iso = event.country_iso

instead of something like:

FROM content 
LEFT OUTER JOIN document ON content.id = document.content_id 
LEFT OUTER JOIN ( 
    SELECT ... FROM event JOIN country AS country_1 ON country_1.iso = event.country_iso
) as event_1 ON event_1.content_id = content.id 

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

so this is because of your innerjoin=True on that relationship. this is a difficult issue to fix and I don't have any solution for it at the moment. you'd need to be explicit about your innerjoin and either turn it off w/ the with_polymorphic or leave it off by default for now.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this is a tricky one and will take some time / thought.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "incorrect join with orm.with_polymorphic() and laz" to "joined eager load innerjoin=True not accommodated "

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.3"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

https://gerrit.sqlalchemy.org/402

this is for 1.2 at the moment, I'm a little nervous about a 1.1 for this because it will change queries from INNER JOIN to OUTER JOIN, if some app is silently relying on this and is not hitting the bug (because they really are loading only one kind of entity) it could cause a sudden performance issue.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed milestone from "1.3" to "1.2"

[sqlalchemy-bot]

Julien Cigar wrote:

thank you very much for the quick fix :-) much appreciated!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Demote innerjoin to outerjoin coming from with_polymorphic

a with_polymorphic, regardless of inheritance type, represents
multiple classes. A subclass that wants to joinedload with innerjoin=True
needs to be demoted to an outerjoin because the parent entity rows
might not be of that type. Looks more intuitive with a joined
inheritance load, but applies just as well to single or concrete.

Change-Id: I4d3d76106ae20032269f8848aad70a8e2f9422f9
Fixes: #3988

→ 94a089b

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed