have Query generate subqueries when adding criterion onto a query which already contains LIMITing criterion

[Former user]

I would like to apply order_by, limit, and then another order_by to a table, so I tried this ORM query:

session.query(T).order_by( T.first ):5.order_by( T.second )

I would expect it to generate a nested SQL query, but instead it produces this:

SELECT t.first, t.second FROM test ORDER BY t.first, t.second LIMIT 5 OFFSET 0

which is wrong, the limit should have been applied before the second order_by, not after it.

[Former user]

The script should print 3,4,5,6,7

[Mike Bayer]

this is an enhancment; Query has no ability, except for a very specific case within eager loading, to generate subqueries. its much simpler than that. its also not necessarily a trivial feature add as it would have to continuously apply translation to all subsequent incoming criterion (such as joins, Class.prop==foo, etc.). translation is always a tricky issue although very recent checkins to trunk which vastly improve its capability to do so may make the feature here not as difficult as it would have been a few months ago.

[Former user]

I agree that the ability to generate subqueries is an enhancement. However, the current behaviour is just wrong. I would expect the second order_by() in the above example to generate a runtime error if subqueries are not yet implemented. Instead, sqlalchemy pretends to support it and produces wrong SQL code. That is a bug, which I think should be easy to fix.

[Mike Bayer]

its concerning to me, since what if someone says:

query(User).order_by(user.c.id).limit(10).offset(20).order_by(user.c.name)

is it a bug then ? I would say its not; you're just building up criterion which will be applied when the query is executed. you can work with Hibernate's query builder object and get the same behavior (it certainly isnt going to construct subqueries). im sure you realize that query(X)3:5 is just direct shorthand for individual limit() and offset() calls, and this is also what the documentation states. So Im still very concerned about having entirely inconsistent behavior between two different ways of doing the same thing, I can see users getting very surprised by it.

going to bring this one to sqlalchemy-devel for review.

[Mike Bayer]

please comment on http://groups.google.com/group/sqlalchemy-devel/browse_thread/thread/98e015b3beb5c6be . theres a few comments on this proposal so far.

[Mike Bayer]

0.5 now enforces that expressions like [:5](:5) are no longer generative; you get a list back immediately. Additionally, Query limits what you can legally call after limit() or offset() is applied and encourages the user to call from_self() if nesting is desired; 4a742751d5d23727d14147578c350d9a61915fe3 adds order_by(), group_by(), and having() to this list.

So while the behavior is not "automatic", the ambiguous action is no longer allowed. I think this is the best approach for now since lots of (personally I'd guess most, but I know there is disagreement on this) users are not expecting limit(2).offset(3).order_by(x) to be any different from order_by(x).limit(2).offset(3).

[Mike Bayer]

Removing milestone: 0.5.0 (automated comment)