Fix label referencing in SQL Server OFFSET logic

Fixed bug where by ROW_NUMBER OVER clause applied for OFFSET
selects in SQL Server would inappropriately substitute a plain column
from the local statement that overlaps with a label name used by
the ORDER BY criteria of the statement.

Change-Id: Ic2500c886cbfc83a1ad5a2681783f008b9f23838
Fixes: #3711

2 files changed, 15 insertions, 1 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 051efa719..966700420 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1155,7 +1155,11 @@ class MSSQLCompiler(compiler.SQLCompiler):
                                        'using an OFFSET or a non-simple '
                                        'LIMIT clause')
 
-            _order_by_clauses = select._order_by_clause.clauses
+            _order_by_clauses = [
+                sql_util.unwrap_label_reference(elem)
+                for elem in select._order_by_clause.clauses
+            ]
+
             limit_clause = select._limit_clause
             offset_clause = select._offset_clause
             kwargs['select_wraps_for'] = select
diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py
index 5f180646c..24c6f5441 100644
--- a/lib/sqlalchemy/sql/util.py
+++ b/lib/sqlalchemy/sql/util.py
@@ -176,6 +176,16 @@ def unwrap_order_by(clause):
     return result
 
 
+def unwrap_label_reference(element):
+    def replace(elem):
+        if isinstance(elem, (_label_reference, _textual_label_reference)):
+            return elem.element
+
+    return visitors.replacement_traverse(
+        element, {}, replace
+    )
+
+
 def expand_column_list_from_order_by(collist, order_by):
     """Given the columns clause and ORDER BY of a selectable,
     return a list of column expressions that can be added to the collist