SQL Server repurposing of ORDER BY -> OVER mis-applies label_reference #3711
Description
Migrated issue, originally created by Michael Bayer (@zzzeek)
the select() element wraps each order by with "label_reference()", which is only appropriate to call upon in the ORDER BY. mssql dialect should unwrap these before it moves the order by into OVER:
from sqlalchemy import *
m = MetaData()
t = Table('t', m, Column('x', Integer))
expr1 = func.foo(t.c.x).label('x')
expr2 = func.foo(t.c.x).label('y')
stmt1 = select([expr1]).order_by(expr1.desc()).offset(1)
stmt2 = select([expr2]).order_by(expr2.desc()).offset(1)
from sqlalchemy.dialects import mssql
print stmt1.compile(dialect=mssql.dialect())
print stmt2.compile(dialect=mssql.dialect())
output:
#!
SELECT anon_1.x
FROM (SELECT foo(t.x) AS x, ROW_NUMBER() OVER (ORDER BY x DESC) AS mssql_rn
FROM t) AS anon_1
WHERE mssql_rn > :param_1
SELECT anon_1.y
FROM (SELECT foo(t.x) AS y, ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn
FROM t) AS anon_1
WHERE mssql_rn > :param_1