incorrect select query when limit is specificed together with order_by

[Former user]

It seems that a wrong SQL query is executed for selects that include a limit keyword together with an order_by.

Apparently, the limit is taken on the unsorted results, and then a sorting is done.

A minimal test case for this is attached.

[Former user]

test case

[Mike Bayer]

the query produced is:

SELECT users.user_id AS users_user_id, users.stats_id AS users_stats_id, stats_6e0c.downloads AS stats_6e0c_downloads, stats_6e0c.stats_id AS stats_6e0c_stats_id, users.name AS users_name 
FROM (SELECT users.user_id AS users_user_id, stats.downloads AS stats_downloads 
FROM users, stats ORDER BY stats.downloads 
 LIMIT 3 OFFSET 0) AS tbl_row_count, users LEFT OUTER JOIN stats AS stats_6e0c ON stats_6e0c.stats_id = users.stats_id 
WHERE tbl_row_count.users_user_id = users.user_id ORDER BY stats_6e0c.downloads, stats_6e0c.oid

the inner query, which is the actual selection query, is correctly using LIMIT with the ORDER BY. however its shoving your "stats" in there without an appropriate join condition since you did not specify one.

the outer query is used for eager loading of related properties and is not intended to be part of the selection criterion in any way; the difference between eager and lazy loading is intended to be transparent to the selection criterion.

what youre looking for is :

print session.query(User).select(Stats.c.stats_id==User.c.stats_id, order_by = [Stats.c.downloads](Stats.c.downloads), limit=3)

which can also be stated as:

q = session.query(User)
print q.select(q.join_to('stats'), order_by = [Stats.c.downloads](Stats.c.downloads), limit=3)

if you check out the SelectResults plugin it can make operations like this a little cleaner.