expression in ORDER BY clause does match any expression in the SELECT clause (SQL Server)

[theosotr]

Describe the bug

I run the following query on SQL Server

expr = (Model.columnA.op('+')(2)).label('label')
session.query(expr).select_from(Model).order_by(desc(expr))).distinct()

SQLalchemy generates the following query

SELECT DISTINCT model.columnA + ? AS label, (model.columnA + ?) AS anon_1
FROM model ORDER BY (model.columnA + ?) DESC

the query above crashes on SQL Server with

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified. (145) (SQLExecDirectW)')

Expected behavior

I expect SQLAlchemy to produce this query instead

SELECT DISTINCT model.columnA + ? AS label
FROM model ORDER BY label DESC

Notably, when i replace the op('+') method, with the Python operator + SQLalchemy produces the expected query. That is

expr = (Model.columnA + 2).label('label')
session.query(expr).select_from(Model).order_by(desc(expr))).distinct()

Have a nice day!

[zzzeek]

any reason you have to use op("+")? there is a mechanism at play that I would have to review in order to describe how it makes its decisions.

[zzzeek]

hi can't reproduce locally please share the relevant parts of "Model", e.g. what datattype for columnA as well as requested SQLAlchemy version in use thanks

[theosotr]

Because op() is the generic mechanism for producing arbitrary operators. This test case is simplified for opening the issue.

[theosotr]

Models

  class Model(Base):
    __tablename__ = 'model'
    id = Column(Integer, primary_key=True)
    columnA = Column(Numeric(10, 2))

SQLAlchemy version that I used: master
But this also fails on 1.3.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Allow Grouping to pass along proxy_set of element https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2103

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the rel_1_3 branch:

Allow Grouping to pass along proxy_set of element https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2104

[zzzeek]

the query above still does not work in 1.3 due to the distinct logic done by Query, seems to not happen in 1.4:

SELECT DISTINCT a.col + ? AS label, (a.col + ?) AS label 
FROM a ORDER BY label DESC

additionally the variant from #5511 doesn't work on either. test for both below

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr

Base = declarative_base()


class Yangon(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    col = Column(Integer)


e = create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server",
    echo=True,
)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)


def test_one():
    expr = (Yangon.col.op("+")(2)).label("label")
    s.query(expr).select_from(Yangon).order_by(desc(expr)).distinct().all()


def test_two():
    expr = Yangon.col + literal(1)
    s.query(expr).select_from(Yangon).order_by(asc(expr)).distinct().all()

[zzzeek]

The recommended approach to this problem in all cases is to name the label explicitly in your ORDER BY. this will work on all versions, please use this approach:

expr = (Yangon.col + literal(1)).label("foo")
q = s.query(expr).select_from(Yangon).order_by(asc("foo")).distinct()
q.all()

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Further fixes for ticket 5470 https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2146

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the rel_1_3 branch:

Further fixes for ticket 5470 https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2147

[zzzeek]

OK that's more fixes. you need to use label(), it won't do the "anon_1" part of it. but all the label() cases should work now.

[theosotr]

Thank you for your prompt fixes.