Re: BUG #4465: GROUP BY is not to SQL standard - Mailing list pgsql-bugs

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