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

The following bug has been logged online:

Bug reference:      4465
Logged by:          Tony Marston
Email address:      tony@marston-home.demon.co.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 p.name and p.price
are functionally dependent on product_id, therefore there is no need for
them to be included in the GROUP BY clause.

In this respect Postgresql is wrong and MySQL is right.

Tony Marston wrote:
> The following bug has been logged online:
>
> Bug reference:      4465
> Logged by:          Tony Marston
> Email address:      tony@marston-home.demon.co.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 p.name 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
specification>.
"""

and as you can read in the documentation
(http://www.postgresql.org/docs/current/static/unsupported-features-sql-standard.html),
PostgreSQL does not support feature T301.

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

I think your definition of "Feature T301 Functional Dependencies" is
extremely questionable. A functional dependency in relational theory
automatically exists where a non-key column on a table is functionally
dependent on the key of that table. It is not something that can be turned
on or off with code, it is built into the design of the table, so it is
erroneous to say that "Postgresql does not support functional dependencies".
If you support both key and non-key columns on a table then you support
functional dependencies whether you like it or not.

As for your statement that PostgreSQL has never claimed that it is fully
SQL-compliant, every time I have posted a message to a PG newsgroup and
compared it with MySQL the immediate response which I receive has always
been along the lines of "don't compare PG with MySQL as that is a toy
database that does not follow the standards". As soon as I point out an SQL
standard that you DON'T follow I get a barrage of weasel words and pathetic
excuses.

Tony Marston

http://www.tonymarston.net=20
http://www.radicore.org



> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net]=20
> Sent: 14 October 2008 20:17
> To: Tony Marston
> Subject: Re: [BUGS] BUG #4465: GROUP BY is not to SQL standard
>=20
>=20
> Tony Marston wrote:
> > I am using document WG3:HBA-003 H2-2003-305 dated August 2003.
> >=20
> > Section 7.9 - 7 of the SQL-1992 standard states:
> >=20
> > "If T is a grouped table, then each <column reference> in=20
> each <value
> > expression> that references a column of T shall reference a=20
> grouping=20
> > expression> column
> > or be specified within a <set function specification>."
> >=20
> > Section 7.12 - 15 of the SQL-2003
> >=20
> > "If T is a grouped table, then let G be the set of grouping=20
> columns of=20
> > T. In each <value expression> contained in <select list>,=20
> each column=20
> > reference that references a column of T shall reference=20
> some column C=20
> > that is functionally dependent on G or shall be contained in an=20
> > aggregated argument of a <set function specification> whose=20
> > aggregation query is QS."
> >=20
> > This means that it is no longer necessary for every column in the=20
> > SELECT list to be specified in the GROUP BY clause. It is=20
> permissible=20
> > to leave out any column which is functionally dependent on=20
> any column=20
> > in the GROUP BY clause. As any non-key column on a table is=20
> > functionally dependent on the table key then provided that=20
> the GROUP=20
> > BY clause contains the table key it is not necessary to specify any=20
> > non-key columns from that table.
>=20
> That assessment is correct, but later Section 7.12=20
> Conformance rule 3 says:
>=20
> "3) Without Feature T301, "Functional dependencies", in=20
> conforming SQL=20
> language, if T is a grouped table, then in each <value expression>=20
> contained in the <select list>, each <column reference> that=20
> references=20
> a column of T shall reference a grouping column or be specified in an=20
> aggregated argument of a <set function specification>."
>=20
> This specialized 7.12 GR 15 to allow only column references=20
> of directly=20
> grouped columns.  In other words, SQL implementations have an=20
> option of=20
> supporting a simpler and a more sophisticated behavior,=20
> distinguished by=20
> feature T301.  Since PostgreSQL does not support T301, we follow the=20
> specification of 7.12 CR 3.  This is perfectly permissible within the=20
> SQL standard.
>=20
> > Postgresql still insists on enforcing the outdated 1992 standard,=20
> > therefore it is wrong to claim that it is SQL-compliant.=20
> That is why I=20
> > am reporting it as a bug.
>=20
> FWIW, PostgreSQL has never claimed that it is fully SQL-compliant.
>=20
>=20

Tony Marston wrote:
> I think your definition of "Feature T301 Functional Dependencies" is
> extremely questionable. A functional dependency in relational theory
> automatically exists where a non-key column on a table is functionally
> dependent on the key of that table. It is not something that can be turned
> on or off with code, it is built into the design of the table, so it is
> erroneous to say that "Postgresql does not support functional dependencies".
> If you support both key and non-key columns on a table then you support
> functional dependencies whether you like it or not.
>
> As for your statement that PostgreSQL has never claimed that it is fully
> SQL-compliant, every time I have posted a message to a PG newsgroup and
> compared it with MySQL the immediate response which I receive has always
> been along the lines of "don't compare PG with MySQL as that is a toy
> database that does not follow the standards". As soon as I point out an SQL
> standard that you DON'T follow I get a barrage of weasel words and pathetic
> excuses.

The issue is that Postgres is _more_ standards-compliant than MySQL, but
Postgres is not 100% compliant either.  Is any database system 100%
compliant?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Tony Marston wrote:
> I think your definition of "Feature T301 Functional Dependencies" is
> extremely questionable. A functional dependency in relational theory
> automatically exists where a non-key column on a table is functionally
> dependent on the key of that table. It is not something that can be turned
> on or off with code, it is built into the design of the table, so it is
> erroneous to say that "Postgresql does not support functional dependencies".
> If you support both key and non-key columns on a table then you support
> functional dependencies whether you like it or not.

I think you misunderstand what "Features" in the SQL standard mean.
Surely a relational database system supports some kind of functional
dependency system, and there is a common definition for that in the
computer science literature (and there is yet another one in the SQL
standard, part 2, clause 4.18).  But in the SQL standard, features
contain conformance claims.  Your product can either conform to SQL:2003
with T301, in which case it should behave as in 7.12 GR 15, or it can
conform to SQL:2003 without T301, in which case it should behave as in
7.12 CR 3.  Both of these can claim to conform to SQL, if they declare
the details correctly.  The fact that T301 is labeled "Functional
dependencies" is an informative label for what the feature generally
tries to achieve, but it is not a normative description of the feature
itself, since that is given elsewhere in the standard.

Since PostgreSQL is documented not to support feature T301, we apply
7.12 CR 3, and no one has so far disputed that we do so incorrectly.

Now you might say, then PostgreSQL is not a real relational database
system.  But I believe it is widely understood that no SQL
implementation implements relational theory correctly.  That's a whole
different can of worms.

> As for your statement that PostgreSQL has never claimed that it is fully
> SQL-compliant, every time I have posted a message to a PG newsgroup and
> compared it with MySQL the immediate response which I receive has always
> been along the lines of "don't compare PG with MySQL as that is a toy
> database that does not follow the standards". As soon as I point out an SQL
> standard that you DON'T follow I get a barrage of weasel words and pathetic
> excuses.

The level of SQL conformance as evaluated by the PostgreSQL developers
can be found in the documentation.  If MySQL has a similar document, you
can draw your own comparisons.  I know of no such document, but I would
guess that MySQL is less conforming than PostgreSQL.

You should, however, not mistake the chatter of the PostgrSQL newsgroup
mob as facts, authorative statements, or representative of the opinions
of the project leadership.  We cannot choose the people our newsgroups
attract.

I am sorry that you interpret my attempts to explain my reading of the
SQL standards to you as weasel words and pathetic excuses.  If you
cannot restrict your comments to rational arguments and have to resort
to name-calling, then I should probably not waste any more time
discussing with you.

"Tony Marston" <tony@marston-home.demon.co.uk> writes:

> I think your definition of "Feature T301 Functional Dependencies" is
> extremely questionable. ... If you support both key and non-key columns on a
> table then you support functional dependencies whether you like it or not.

An ISO/IEC 9075 conformant implementation must list in its documentation which
optional features it claims to support. Postgres does so at:

http://www.postgresql.org/docs/8.3/static/features.html

In particular note that T301 is listed partway down this list:

http://www.postgresql.org/docs/8.3/static/unsupported-features-sql-standard.html


> As soon as I point out an SQL standard that you DON'T follow I get a barrage
> of weasel words and pathetic excuses.

Well then.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

You are still missing the point - "functional dependencies" is not a
separate module that can be turned on or off with code, they are inherent in
the database design. According to relational theory any non-key field on a
table is functionally dependent of the key of that table, so if you support
both key and non-key fields on a table then you automatically support
functional dependencies. How can you possibly say otherwise?

Where does it describe in the SQL standards EXACTLY what the term
"functional dependencies" means? Is it the same as in relational theory, or
is it something different?

Tony Marston

http://www.tonymarston.net=20
http://www.radicore.org



> -----Original Message-----
> From: Greg Stark [mailto:greg.stark@enterprisedb.com] On=20
> Behalf Of Gregory Stark
> Sent: 15 October 2008 02:57
> To: Tony Marston
> Cc: 'Peter Eisentraut'; pgsql-bugs@postgresql.org
> Subject: Re: BUG #4465: GROUP BY is not to SQL standard
>=20
>=20
> "Tony Marston" <tony@marston-home.demon.co.uk> writes:
>=20
> > I think your definition of "Feature T301 Functional=20
> Dependencies" is=20
> > extremely questionable. ... If you support both key and non-key=20
> > columns on a table then you support functional dependencies whether=20
> > you like it or not.
>=20
> An ISO/IEC 9075 conformant implementation must list in its=20
> documentation which optional features it claims to support.=20
> Postgres does so at:
>=20
> http://www.postgresql.org/docs/8.3/static/features.html
>=20
> In particular note that T301 is listed partway down this list:
>=20
> http://www.postgresql.org/docs/8.3/static/unsupported-features
-sql-standard.html


> As soon as I point out an SQL standard that you DON'T follow I get a=20
> barrage of weasel words and pathetic excuses.

Well then.

--=20
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

Tony Marston wrote:
> You are still missing the point - "functional dependencies" is not a
> separate module that can be turned on or off with code,

It is in the SQL standard.

> they are inherent in
> the database design. According to relational theory any non-key field on a
> table is functionally dependent of the key of that table, so if you support
> both key and non-key fields on a table then you automatically support
> functional dependencies. How can you possibly say otherwise?

Again, you are confusing the SQL standard with relational theory, and an
SQL standard conformance feature name with the computer science
interpretation of that name.  I suggest you read Part 1 "Framework" of
SQL 2003 which explains how SQL conformance works.

> Where does it describe in the SQL standards EXACTLY what the term
> "functional dependencies" means? Is it the same as in relational theory, or
> is it something different?

We are talking about feature T301, which is defined by the Conformance
rules that mention it.  That is all that matters.  It does not matter
what the name of that feature is.

Support for functional dependencies is not a feature that can be turned off
in any database engine. Dependencies, whether they are functional,
transitive, multi-valued or join dependencies, are inherent in the database
design. It is therefore nonsense to say that support for functional
dependencies is optional.

Saying that the SQL standard and Relational Theory are unconnected is
complete misdirection. The SQL standard is surely there to define how
Relational Theory can/should be implemented. A database cannot be classed as
"relational" if it does not support standard SQL, therefore the two must go
hand in hand.

The simple fact is that it is only in the 1992 standard that it states that
ALL columns in the SELECT clause must be identified in the GROUP BY clause.
In all subsequent standards it has been permissible to omit any column from
the GROUP BY clause if it is functionally dependent on any other column in
the GROUP BY clause. All you can do is point to paragraphs which are NOT
contained in the definition of the GROUP BY clause and say "this is our get
out". Now you wonder why I refer to your arguments as "weasel words".=20

Tony Marston

http://www.tonymarston.net=20
http://www.radicore.org



> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net]=20
> Sent: 15 October 2008 11:53
> To: Tony Marston
> Cc: 'Gregory Stark'; pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #4465: GROUP BY is not to SQL standard
>=20
>=20
> Tony Marston wrote:
> > You are still missing the point - "functional dependencies"=20
> is not a=20
> > separate module that can be turned on or off with code,
>=20
> It is in the SQL standard.
>=20
> > they are inherent in
> > the database design. According to relational theory any=20
> non-key field=20
> > on a table is functionally dependent of the key of that=20
> table, so if=20
> > you support both key and non-key fields on a table then you=20
> > automatically support functional dependencies. How can you possibly=20
> > say otherwise?
>=20
> Again, you are confusing the SQL standard with relational=20
> theory, and an=20
> SQL standard conformance feature name with the computer science=20
> interpretation of that name.  I suggest you read Part 1=20
> "Framework" of=20
> SQL 2003 which explains how SQL conformance works.
>=20
> > Where does it describe in the SQL standards EXACTLY what the term=20
> > "functional dependencies" means? Is it the same as in relational=20
> > theory, or is it something different?
>=20
> We are talking about feature T301, which is defined by the=20
> Conformance=20
> rules that mention it.  That is all that matters.  It does not matter=20
> what the name of that feature is.
>=20
>=20

On Wed, Oct 15, 2008 at 01:47:40PM +0100, Tony Marston wrote:

> Support for functional dependencies is not a feature that can be
> turned off in any database engine.

Repeating the same premise over and over again does not constitute an
argument.  In this case, you appear to be begging the question.  I
think you have your answer, even if you don't like it.

A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Tue, Oct 14, 2008 at 08:28:42PM -0400, Bruce Momjian wrote:
> Tony Marston wrote:
> > I think your definition of "Feature T301 Functional Dependencies"
> > is extremely questionable. [et cetæra, ad nauseam]
>
> The issue is that Postgres is _more_ standards-compliant than MySQL,
> but Postgres is not 100% compliant either.  Is any database system
> 100% compliant?

No.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

[Note: I'm *not* an expert in the SQL standard, but I might be able to
help clarify a misunderstanding or two in the discussion so far].

Tony Marston wrote:
> Support for functional dependencies is not a feature that can be turned off
> in any database engine. Dependencies, whether they are functional,
> transitive, multi-valued or join dependencies, are inherent in the database
> design.

Well, my understanding is that they're a core part of relational theory,
anyway. This doesn't necessarily make them "inherent in the database
design" - in that AFAIK no computer database implementation fully
implements the correct and complete relational calculus.

In a sense, all relational database implementations can be considered
"incorrect" - they can all produce results that wouldn't be permitted by
a strict relational implementation. On the upside, they actually produce
the results this century, and correctness can still be assured by manual
locking in critical situations.

If there really is a strict and pure relational implementation, I'd be
curious to know about it. Does it work? Does it work in the real world?

> It is therefore nonsense to say that support for functional
> dependencies is optional.

Again, that's true for the theory, but practical implementations may not
achieve theoretical perfection. SQL is a standard describing
requirements and optional features fo a practial implementation of a
relational database engine, not a description of relational theory.

> Saying that the SQL standard and Relational Theory are unconnected is
> complete misdirection. The SQL standard is surely there to define how
> Relational Theory can/should be implemented.

You have used the word "surely" - in this case, as conjecture. People
here have already told you that the SQL standard does NOT describe a
strict implementation of relational theory, and that an SQL-confirming
implementation may lack features like predicate locking, identification
of functional dependencies in result sets, etc.

> A database cannot be classed as
> "relational" if it does not support standard SQL, therefore the two must go
> hand in hand.

Er, no. Support for standard SQL does not imply a strict implementation
of relational theory (ie being "classed as relational"), as the SQL
standard does not actually describe a strict implementation of
relational theory. I don't know enough about the standard to be sure,
but I wouldn't be too surprised if a fully conformant SQL implementation
could *not* strictly implement relational theory.

> The simple fact is that it is only in the 1992 standard that it states that
> ALL columns in the SELECT clause must be identified in the GROUP BY clause.
> In all subsequent standards it has been permissible to omit any column from
> the GROUP BY clause if it is functionally dependent on any other column in
> the GROUP BY clause. All you can do is point to paragraphs which are NOT
> contained in the definition of the GROUP BY clause and say "this is our get
> out". Now you wonder why I refer to your arguments as "weasel words".

What's been said in response to your initial post all looks pretty
reasonable to me, and I see attempts to explain the situation rather
than "weasel".

Standards aren't generally the perfect ideals we might want them to be,
and the SQL standard is full of optional features, practical
compromises, and so on. As Peter Eisentraut explained in reply to your
initial post, PostgreSQL does not support one of the optional features
in the SQL-99/SQL-2003 standard, namely T301, and retains SQL-92
behaviour in this area. That's perfectly acceptable according to the
standard.

The definition of functional dependencies in T301 that you are arguing
about is a definition in the SQL standard that PostgreSQL attempts to
follow, not a definition made up by the PostgreSQL developers. If you
don't like the definition, you'll need to take that up with the SQL
standards body. If it contradicts relational theory, then that's either
a practical compromise or an error made by the SQL standards body.

It would be nice if PostgreSQL did support that feature. However, it can
be compliant with the SQL standard without it, as it's an optional
feature. (In fact I don't know if PostgreSQL *is* fully compliant with
any of the SQL standards, but that's another issue).

Arguably no SQL database should be described as "relational" - but in
practice, SQL describes a good and workable approximation of relational
theory that works in the real world and is useful to describe as
"relational" to distinguish it from other, completely different,
database designs.

--
Craig Ringer