Great; do you know when this will be released? I'm unfamiliar with
SQLAlchemy's release cadence.
On Tuesday, May 10, 2016 at 10:22:48 AM UTC-5, 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:
>
> 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.
