joins + contains_eager + limit on self referral relationships produce invalid SQL

[Former user]

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

[Mike Bayer]

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:

query = session.query(Entry)\
            .outerjoin((ParentEntry, Entry.parent_entry_id == ParentEntry.id ))\
            .options(lazyload('parent_user'), lazyload('parent_entry.parent_user'))\
            .options(contains_eager('parent_entry',alias = ParentEntry)).limit(10).offset(3)

[Former user]

Cool, thanks for the response! Alex

[Mike Bayer]

this wasn't so hard to fix and now works in a9a4da62cfe4a7dbe6afff1f993e161e8243a6dc.

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)