1. Victor Olex
  2. sqlalchemy

Commits

Mike Bayer  committed b673325

- [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]

  • Participants
  • Parent commits 0fcd8b4
  • Branches default

Comments (0)

Files changed (3)

File CHANGES

View file
     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

File lib/sqlalchemy/dialects/mssql/base.py

View file
         """
         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')

File test/dialect/test_mssql.py

View file
             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))