LIMIT/OFFSET workaround for mssql loses auto-correlation for the ORDER BY clause
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.
Comments (10)
-
reporter -
repo owner 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')
-
repo owner -
reporter With the change in comment:1, I got exception:
AttributeError: 'str' object has no attribute '_compiler_dispatch'
-
repo owner OK, I'd gather maybe some tests pass a string for order_by, will have to play with it.
(testing trac comment editing)
-
reporter A patch against 0.7.8 that doesn't add the extra parenthesis
-
reporter 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. -
repo owner - marked as critical
-
repo owner - changed status to resolved
that last patch is great. this is committed in 0.7, 0.8.
e35a6b91d671d86ee8474257f4df3ec98a054f39 0d41daa50987ec36b1c681a45a56c09e9cf00aae
-
repo owner - removed milestone
Removing milestone: 0.7.9 (automated comment)
- Log in to comment
The diff that works for me