Sok Ann Yap (@sayap) wrote:

The diff that works for me

Changes by Sok Ann Yap (@sayap):

• attached file mssql-limit_offset-correlated_order_by.diff

Michael Bayer (@zzzeek) wrote:

OK just curious, can you test that this does it also, as an option to avoid those parens for now (there's a different fix for that in 0.8):

diff -r 30e8cd5960de3c2c72d60ee51ade31232775d76a lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py	Tue Jul 17 09:32:11 2012 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py	Tue Jul 24 12:01:13 2012 -0400
@@ -821,7 +821,7 @@
             select._mssql_visit = True
             select = select.column(
                 sql.literal_column("ROW_NUMBER() OVER (ORDER BY %s)" \
-                % orderby).label("mssql_rn")
+                % self.process(orderby, **kwargs)).label("mssql_rn")
                                    ).order_by(None).alias()
 
             mssql_rn = sql.column('mssql_rn')

Changes by Michael Bayer (@zzzeek):

• removed labels: low priority
• set milestone to "0.7.9"

Sok Ann Yap (@sayap) wrote:

With the change in comment:1, I got exception:

AttributeError: 'str' object has no attribute '_compiler_dispatch'

Michael Bayer (@zzzeek) wrote:

OK, I'd gather maybe some tests pass a string for order_by, will have to play with it.

(testing trac comment editing)

Sok Ann Yap (@sayap) wrote:

A patch against 0.7.8 that doesn't add the extra parenthesis

Changes by Sok Ann Yap (@sayap):

• attached file mssql-limit_offset-correlated_order_by-r1.diff

Sok Ann Yap (@sayap) wrote:

The extra parens added by my original diff breaks ORDER BY with DESC, as it generate clauses such as ROW_NUMBER() OVER (ORDER BY (xxx.id DESC)). I have attached another diff without this problem.

Changes by Michael Bayer (@zzzeek):

• added labels: high priority

Michael Bayer (@zzzeek) wrote:

that last patch is great. this is committed in 0.7, 0.8.

e35a6b9
0d41daa

Changes by Michael Bayer (@zzzeek):

• changed status to closed