On 05/30/2017 07:42 AM, Christopher Wilson wrote:
Dear Michael and fellow SQLAlchemy users/developers,
I think I have found an issue with the use of distinct() over specified
columns on a Query:
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.distinct
As the Postgres documentation notes, this is a non-standard Postgres
extension to SQL, so we should not expect other databases to support it:
https://www.postgresql.org/docs/9.0/static/sql-select.html (see
Nonstandard Clauses at the end of the page).
And the SQLAlchemy documentation describes the observed behaviour, but
that is still surprising behaviour:
*distinct*(/*criterion/)
Apply a DISTINCT to the query and return the newly resulting Query.
*Note*
The distinct()
<http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.interfaces.PropComparator.distinct> call
includes logic that will automatically add columns from the ORDER BY of
the query to the columns clause of the SELECT statement, to satisfy the
common need of the database backend that ORDER BY columns be part of the
SELECT list when DISTINCT is used. These columns /are not/ added to the
list of columns actually fetched by the Query
<http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query>,
however, so would not affect results. The columns are passed through
when using the Query.statement
<http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.statement> accessor,
however.
*Parameters:*
**expr* – optional column expressions. When present, the PostgreSQL
dialect will render a DISTINCTON (<expressions>>) construct.
It doesn’t explicitly say it, but this statement allows the DISTINCT
columns to be completely ignored by other database backends, and indeed
it appears that they are. For example when using the MS SQL dialect, the
following code:
s = Session()
s.bind.echo = True
s.query(DS2CtryQtInfo).distinct(DS2CtryQtInfo.info_code).first()
Runs this query:
SELECT DISTINCT TOP 1 [DS2CtryQtInfo].[InfoCode] AS
[DS2CtryQtInfo_InfoCode], …
FROM [DS2CtryQtInfo]
Therefore the engine silently executed a query that was different than
the requested one (i.e. wrong), as though I had requested .distinct()
instead of .distinct(DS2CtryQtInfo.info_code).
Please could I request that engines raise an exception if the user tries
to use a feature which isn’t supported by the engine?
the best we can do is warn as this has been there forever,
https://bitbucket.org/zzzeek/sqlalchemy/issues/4002/warn-when-postgresql-distinct-columns-used
is created.
Thanks, Chris.
------------------------------------------------------------------------
/This email is confidential. If you are not the intended recipient,
please advise us immediately and delete this message. The registered
name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP.
See - http://www.gam.com/en/Legal/Email+disclosures+EU for further
information on confidentiality, the risks of non-secure electronic
communication, and certain disclosures which we are required to make in
accordance with applicable legislation and regulations. If you cannot
access this link, please notify us by reply message and we will send the
contents to you.
------------------------------------------------------------------------
///
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
