On 05/10/2016 01:10 PM, Craig Weber wrote:
Great; do you know when this will be released? I'm unfamiliar with
SQLAlchemy's release cadence.
the minor fix releases generally come as a particular point version has
either had about 6-8 weeks in development, or if it accumulates any
non-workaround-capable issues. This issue probably can be worked
around however there are other non-workaroundable fixes in the release
already so for 1.0.13 I was hoping to release today or later in the week.
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.[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.
