Hello,
I believe I've encountered a bug, but I wanted to verify it here before
polluting the issue tracker.
When I compile the following SQLAlchemy query for postgres, everything
seems to work as expected; however, it fails for MSSQL.
price_sum = sqlalchemy.func.sum(
table.c['Opportunity Max Amount']
).label('Opportunity Max Amount')
query = sqlalchemy.select(
columns=[price_sum],
order_by=sqlalchemy.desc(price_sum),
offset=1,
).alias("FOO")
rows = conn.execute(query).fetchall()
It seems the generated query is attempting to order by the un-aggregated
column rather than the specified column.
Here is the generated MSSQL query:
SELECT anon_1.[Opportunity Max Amount]
FROM (
SELECT
sum([table_name].[Opportunity Max Amount]) AS [Opportunity Max Amount],
ROW_NUMBER() OVER (ORDER BY [Opportunity Max Amount] DESC) AS mssql_rn
FROM [table_name]
) AS anon_1
WHERE mssql_rn > 1
Here's the error message:
sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (8120, b"Column
'80079bd5c7664c5eb6789fbfa49fd10b.Opportunity Max Amount' is invalid in the
select list because it is not contained in either an aggregate function or the
GROUP BY clause.DB-Lib error message 20018, severity 16:\nGeneral SQL Server
error: Check messages from the SQL Server\n") [SQL: 'SELECT anon_1.[Opportunity
Max Amount] \nFROM (SELECT sum([80079bd5c7664c5eb6789fbfa49fd10b].[Opportunity
Max Amount]) AS [Opportunity Max Amount], ROW_NUMBER() OVER (ORDER BY
[Opportunity Max Amount] DESC) AS mssql_rn \nFROM
[80079bd5c7664c5eb6789fbfa49fd10b]) AS anon_1 \nWHERE mssql_rn > %(param_1)s']
[parameters: {'param_1': 1}]
And here is the working Postgres query for reference:
SELECT
sum(performance_data."Opportunity Max Amount") AS "Opportunity Max Amount"
FROM performance_data
ORDER BY "Opportunity Max Amount" DESC
LIMIT ALL
OFFSET 1
Thanks,
Craig
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
