LIMIT/OFFSET workaround for mssql loses auto-correlation for the ORDER BY clause

[Sok Ann Yap]

With the LIMIT/OFFSET workaround for mssql, there is no auto-correlation for the ORDER BY clause, as it is compiled separately.

Seems to work as intended if I get the whole SELECT to compile together using the over() method added in version 0.7.

[Sok Ann Yap]

The diff that works for me

[Mike Bayer]

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')

[Sok Ann Yap]

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

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

[Mike Bayer]

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]

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

[Sok Ann Yap]

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.

[Mike Bayer]

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

e35a6b91d671d86ee8474257f4df3ec98a054f39 0d41daa50987ec36b1c681a45a56c09e9cf00aae

[Mike Bayer]

Removing milestone: 0.7.9 (automated comment)