Re: [sqlalchemy] .distinct() silently ignored for DBs other than Postgres

2017-05-30 Thread 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() 
 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 (>) 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 
.
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.


--
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.

[sqlalchemy] .distinct() silently ignored for DBs other than Postgres

2017-05-30 Thread 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 (>) 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.

-- 
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.