- [bug] Fixed compiler bug whereby a given

select() would be modified if it had an "offset"
attribute, causing the construct to not compile
correctly a second time.  [ticket:2545]

4 files changed, 23 insertions, 13 deletions

diff --git a/CHANGES b/CHANGES
index 9830420a4..be1d4e82e 100644
--- a/CHANGES
+++ b/CHANGES
@@ -540,6 +540,12 @@ are also present in 0.8.
     the "name" and "native_enum" flags.  Helps
     Alembic autogenerate.
 
+- mssql
+  - [bug] Fixed compiler bug whereby a given
+    select() would be modified if it had an "offset"
+    attribute, causing the construct to not compile
+    correctly a second time.  [ticket:2545]
+
 0.7.8
 =====
 - orm
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 91f396d23..cc50fbe6b 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -787,7 +787,7 @@ class MSSQLCompiler(compiler.SQLCompiler):
         so tries to wrap it in a subquery with ``row_number()`` criterion.
 
         """
-        if not getattr(select, '_mssql_visit', None) and select._offset:
+        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:
@@ -796,6 +796,7 @@ class MSSQLCompiler(compiler.SQLCompiler):
 
             _offset = select._offset
             _limit = select._limit
+            select = select._generate()
             select._mssql_visit = True
             select = select.column(
                 sql.literal_column("ROW_NUMBER() OVER (ORDER BY %s)" \
@@ -804,10 +805,10 @@ class MSSQLCompiler(compiler.SQLCompiler):
 
             mssql_rn = sql.column('mssql_rn')
             limitselect = sql.select([c for c in select.c if
-                                        c.key!='mssql_rn'])
-            limitselect.append_whereclause(mssql_rn> _offset)
+                                        c.key != 'mssql_rn'])
+            limitselect.append_whereclause(mssql_rn > _offset)
             if _limit is not None:
-                limitselect.append_whereclause(mssql_rn<=(_limit + _offset))
+                limitselect.append_whereclause(mssql_rn <= (_limit + _offset))
             return self.process(limitselect, iswrapper=True, **kwargs)
         else:
             return compiler.SQLCompiler.visit_select(self, select, **kwargs)
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index cbeac7791..3c6ec55af 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -566,7 +566,7 @@ class OracleCompiler(compiler.SQLCompiler):
                     if not self.dialect.use_binds_for_limits:
                         max_row = sql.literal_column("%d" % max_row)
                     limitselect.append_whereclause(
-                            sql.literal_column("ROWNUM")<=max_row)
+                            sql.literal_column("ROWNUM") <= max_row)
 
                 # If needed, add the ora_rn, and wrap again with offset.
                 if select._offset is None:
diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py
index 7a6f2665e..1c6f8d02a 100644
--- a/test/dialect/test_mssql.py
+++ b/test/dialect/test_mssql.py
@@ -426,14 +426,17 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
 
         s = select([t]).where(t.c.x==5).order_by(t.c.y).offset(20)
 
-        self.assert_compile(
-            s,
-            "SELECT anon_1.x, anon_1.y FROM (SELECT t.x AS x, t.y "
-            "AS y, ROW_NUMBER() OVER (ORDER BY t.y) AS "
-            "mssql_rn FROM t WHERE t.x = :x_1) AS "
-            "anon_1 WHERE mssql_rn > :mssql_rn_1",
-            checkparams={u'mssql_rn_1': 20, u'x_1': 5}
-        )
+        # test that the select is not altered with subsequent compile
+        # calls
+        for i in xrange(2):
+            self.assert_compile(
+                s,
+                "SELECT anon_1.x, anon_1.y FROM (SELECT t.x AS x, t.y "
+                "AS y, ROW_NUMBER() OVER (ORDER BY t.y) AS "
+                "mssql_rn FROM t WHERE t.x = :x_1) AS "
+                "anon_1 WHERE mssql_rn > :mssql_rn_1",
+                checkparams={u'mssql_rn_1': 20, u'x_1': 5}
+            )
 
     def test_limit_offset_using_window(self):
         t = table('t', column('x', Integer), column('y', Integer))