- [bug] Fixed compiler bug whereby using a correlated

    subquery within an ORDER BY would fail to render correctly
    if the stament also used LIMIT/OFFSET, due to mis-rendering
    within the ROW_NUMBER() OVER clause.  Fix courtesy
    sayap [ticket:2538]

3 files changed, 31 insertions, 4 deletions

diff --git a/CHANGES b/CHANGES
index 7f871d7d4..a264aa152 100644
--- a/CHANGES
+++ b/CHANGES
@@ -949,6 +949,12 @@ are also present in 0.8.
     courtesy David McNelis.
 
 - mssql
+  - [bug] Fixed compiler bug whereby using a correlated
+    subquery within an ORDER BY would fail to render correctly
+    if the stament also used LIMIT/OFFSET, due to mis-rendering
+    within the ROW_NUMBER() OVER clause.  Fix courtesy
+    sayap [ticket:2538]
+
   - [bug] Fixed compiler bug whereby a given
     select() would be modified if it had an "offset"
     attribute, causing the construct to not compile
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 03bbf6446..755237587 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -789,18 +789,18 @@ class MSSQLCompiler(compiler.SQLCompiler):
         """
         if select._offset and not getattr(select, '_mssql_visit', None):
             # to use ROW_NUMBER(), an ORDER BY is required.
-            orderby = self.process(select._order_by_clause)
-            if not orderby:
+            if not select._order_by_clause.clauses:
                 raise exc.CompileError('MSSQL requires an order_by when '
                                               'using an offset.')
 
             _offset = select._offset
             _limit = select._limit
+            _order_by_clauses = select._order_by_clause.clauses
             select = select._generate()
             select._mssql_visit = True
             select = select.column(
-                sql.literal_column("ROW_NUMBER() OVER (ORDER BY %s)" \
-                % orderby).label("mssql_rn")
+                 sql.func.ROW_NUMBER().over(order_by=_order_by_clauses)
+                     .label("mssql_rn")
                                    ).order_by(None).alias()
 
             mssql_rn = sql.column('mssql_rn')
diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py
index 6b2684888..bf4e93758 100644
--- a/test/dialect/test_mssql.py
+++ b/test/dialect/test_mssql.py
@@ -448,6 +448,27 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             checkparams={u'mssql_rn_1': 20, u'mssql_rn_2': 30, u'x_1': 5}
         )
 
+    def test_limit_offset_with_correlated_order_by(self):
+        t1 = table('t1', column('x', Integer), column('y', Integer))
+        t2 = table('t2', column('x', Integer), column('y', Integer))
+
+        order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar()
+        s = select([t1]).where(t1.c.x == 5).order_by(order_by) \
+            .limit(10).offset(20)
+
+        self.assert_compile(
+            s,
+            "SELECT anon_1.x, anon_1.y "
+            "FROM (SELECT t1.x AS x, t1.y AS y, "
+            "ROW_NUMBER() OVER (ORDER BY "
+            "(SELECT t2.y FROM t2 WHERE t1.x = t2.x)"
+            ") AS mssql_rn "
+            "FROM t1 "
+            "WHERE t1.x = :x_1) AS anon_1 "
+            "WHERE mssql_rn > :mssql_rn_1 AND mssql_rn <= :mssql_rn_2",
+            checkparams={u'mssql_rn_1': 20, u'mssql_rn_2': 30, u'x_1': 5}
+        )
+
     def test_limit_zero_offset_using_window(self):
         t = table('t', column('x', Integer), column('y', Integer))