On 05/10/2016 11:22 AM, Craig Weber wrote:
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:
bug is created at https://bitbucket.org/zzzeek/sqlalchemy/issues/3711
and the patch is against CI review right now. commit should be in 30
minutes.
|
|SELECT anon_1.[OpportunityMaxAmount]
FROM (
SELECT
sum([table_name].[OpportunityMaxAmount])AS [OpportunityMaxAmount],
ROW_NUMBER()OVER (ORDER BY [OpportunityMaxAmount]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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
