.distinct(<cols>) silently ignored for DBs other than Postgres

[Christopher Wilson]

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() 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, however, so would not affect results. The columns are passed through when using the 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?

 

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.

---

[mike bayer]

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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.