Skip site navigation (1) Skip section navigation (2)

Re: BUG #4465: GROUP BY is not to SQL standard

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Tony Marston <tony(at)marston-home(dot)demon(dot)co(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4465: GROUP BY is not to SQL standard
Date: 2008-10-10 16:56:30
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Tony Marston wrote:
> The following bug has been logged online:
> Bug reference:      4465
> Logged by:          Tony Marston
> Email address:      tony(at)marston-home(dot)demon(dot)co(dot)uk
> PostgreSQL version: 8.3.4
> Operating system:   Windows XP
> Description:        GROUP BY is not to SQL standard
> Details: 
> The Postgresql implementation of GROUP BY does not conform to either the
> 1999 or 2003 SQL standard. The documentation states that every field in the
> SELECT list which is not aggregated must be specified in the GROUP BY
> clause. While this was true in the 1992 standard, in 1999 this was changed
> to "any non-aggregated column appearing in the SELECT list is functionally
> dependent upon the GROUP BY clause". In the example both and p.price
> are functionally dependent on product_id, therefore there is no need for
> them to be included in the GROUP BY clause.

SQL 2003 (and similarly 1999) also says

Without Feature T301, “Functional dependencies”, in conforming SQL 
language, if T is a grouped table,
then in each <value expression> contained in the <select list>, each 
<column reference> that references a
column of T shall reference a grouping column or be specified in an 
aggregated argument of a <set function

and as you can read in the documentation 
PostgreSQL does not support feature T301.

Therefore the implemented behavior is not "wrong", but it could arguably 
be enhanced.

In response to

pgsql-bugs by date

Next:From: Jussi PakkanenDate: 2008-10-10 19:55:10
Subject: Re: BUG #4462: Adding COUNT to query causes massive slowdown
Previous:From: Tony MarstonDate: 2008-10-10 09:47:59
Subject: BUG #4465: GROUP BY is not to SQL standard

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group