- marked as enhancement
- changed milestone to 0.5.xx
joins + contains_eager + limit on self referral relationships produce invalid SQL
Issue #1180
resolved
Steps required to reproduce the issue:
Create query on an object with self referral relations, using alias, eager_load, and limit statement on the query and it will give you SQL that database do not understand. (the case is attached)
Once you remove limit and offset, the query is OK, otherwise some strange query is generated
My config: Mysql 5.1, slqalchemy 5.0 beta2
Comments (4)
-
repo owner -
Account Deleted Cool, thanks for the response! Alex
-
repo owner - changed status to resolved
this wasn't so hard to fix and now works in a9a4da62cfe4a7dbe6afff1f993e161e8243a6dc.
-
repo owner - removed milestone
Removing milestone: 0.5.xx (automated comment)
- Log in to comment
I'd classify this as "enhancement", since
contains_eager()
was not designed to be used with SQL statements that also contain ORM-generated joins - the intent was to support an entirely end-user constructed SQL statement.The eager load of "parent_user" doesn't know how to navigate around the LIMITED subquery of your custom selectable (which is the join of Entry to an alias of itself). By allowing the parent_user eagerload to attempt to modify the query, things get tripped up. This doesn't occur with the second test case because the eager load of Entry to Entry never takes place, because you haven't specified join_depth. If you do specify join_depth, the second example appears to produce an incorrect query as well (though MySQL allows it).
Workaround for the immediate example is: