From 0d41daa50987ec36b1c681a45a56c09e9cf00aae Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 30 Sep 2012 16:57:23 -0400 Subject: [PATCH] - [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] --- CHANGES | 6 ++++++ lib/sqlalchemy/dialects/mssql/base.py | 8 ++++---- test/dialect/test_mssql.py | 21 +++++++++++++++++++++ 3 files changed, 31 insertions(+), 4 deletions(-) diff --git a/CHANGES b/CHANGES index b1bf31a8b3..1cc2eee98f 100644 --- a/CHANGES +++ b/CHANGES @@ -187,6 +187,12 @@ CHANGES 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 816be76b19..c544d1d48e 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -811,18 +811,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 7285e96364..48d989d041 100644 --- a/test/dialect/test_mssql.py +++ b/test/dialect/test_mssql.py @@ -455,6 +455,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)) -- 2.47.2