Extraneous join added when there is a limit on eagerloading query

[Former user]

(original reporter: ants) When using a limit on query that includes a eagerload the primary key of the mapper is selected in a subquery with a limit and then joined with itself to get the values of the columns. That join could be avoided if the columns would be selected straight from the subquery. That is, instead of

SELECT foo.id AS foo_id, foo.data AS foo_data, bar_1.id AS bar_1_id, bar_1.foo_id AS bar_1_foo_id
FROM (SELECT foo.id AS foo_id, foo.id AS foo_oid
FROM foo ORDER BY foo.id
 LIMIT 10 OFFSET 0) AS tbl_row_count, foo LEFT OUTER JOIN bar AS bar_1 ON foo.id = bar_1.foo_id
WHERE foo.id = tbl_row_count.foo_id ORDER BY tbl_row_count.foo_id, bar_1.id

do

SELECT tbl_row_count.foo_id AS foo_id, tbl_row_count.foo_data AS foo_data, bar_1.id AS bar_1_id, bar_1.foo_id AS bar_1_foo_id
FROM (SELECT foo.id AS foo_id, foo.data AS foo_data
FROM foo ORDER BY foo.id
 LIMIT 10 OFFSET 0) AS tbl_row_count LEFT OUTER JOIN bar AS bar_1 ON tbl_row_count.foo_id = bar_1.foo_id
ORDER BY tbl_row_count.foo_id, bar_1.id

[Mike Bayer]

attached is the work so far with this, just as a diff. its a pretty serious change since it necessarily adds a "row translate" step to query.instances() (tried working around that requirement by naming the subquery the same as that of the table, but this doesnt work for joined-table inheritance where the "table" is just "A JOIN B" without a n explicit name). this adds function call overhead for eager queries that have ranges but this might be less significant than the overhead saved by losing an extra join table.

it also involved further changes to expression translation so that the "A LEFT OUTER JOIN B ON A.id=B.id" joins that eager loading adds could be properly translated (Alias objects become immutable, select objects need to implement "is_derived_from()" more completely).

the patch isnt done yet, advanced eager load tests dont pass and at least one goes into an endless loop.

[Mike Bayer]

high priority since there are some important refactorings and behavior changes to expressions i want to integrate here.

[Mike Bayer]

merged final part of the branch in 8ebc8ee5bae0f4bef465e8b5b4b46609cfdebc10. this involved a rewrite of AliasedClauses and a bunch of other things so it was a fairly major change.

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)