SQL Server repurposing of ORDER BY -> OVER mis-applies label_reference

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

Comments (4)