Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>,
	Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [SQL] 7.3 GROUP BY differs from 7.2
Date: Sun, 23 Feb 2003 12:19:08 -0800
User-Agent: KMail/1.4.3
Cc: Dan Langille <dan@langille.org>, <pgsql-bugs@postgresql.org>,
	<pgsql-sql@postgresql.org>
References: <20030222091532.F73514-100000@megazone23.bigpanda.com>
In-Reply-To: <20030222091532.F73514-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Message-Id: <200302231219.08727.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable

Guys,

SQL spec aside, thinking about this from a strictly implementation/user poi=
nt=20
of view:
(an keeping in mind that I think it's very important that we work out the=
=20
spec-correct behaviour for 7.4 and/or 7.3.3)

The particular case that Dan has raised is an issue for four reasons:
1) It looks to a human like it *should* work, and I think given a long week=
end=20
of relational calculus someone (not me) could define the cases where it is =
OK=20
as opposed to the cases (probably the majority) where it is not.
2) That syntax *did* work in previous versions of PostgreSQL.
3) That syntax will be accepted by some other SQL databases.
4) The error message is rather confusing and could cause a developer to spe=
nd=20
an hour or more hunting for the wrong error.

I propose that, should we decide not to change the behaviour of the parser,=
=20
that we do the following:

1) add the following to the FAQ or elsewhere:

Q.  I just got the message "ERROR:  Attribute unnamed_join.[column name] mu=
st=20
be GROUPed or used in an aggregate function" and my GROUP BY query won't ru=
n,=20
even though all of the columns are in the GROUP BY clause. This query may=
=20
have worked in PostgreSQL 7.2, or on another SQL database.  What do I do?

A. You are probably qualifying a column name differently in the SELECT clau=
se=20
than in the GROUP BY clause, causing the parser to be confused about what y=
ou=20
really mean.   For example, you may be referring to a column by its simple=
=20
column name ("element_id") in the SELECT clause, and by its table-qualified=
=20
name ("table1.element_id") in the GROUP BY clause, or you may be using an=
=20
alias in one place but not the other.   Please make sure that all columns i=
n=20
the SELECT clause match *exactly* the columns in the GROUP BY clause.

2) That we add a warning in the 7.3 release notes about the breaking of=20
backward compatibility.

Thoughts?

--=20
Josh Berkus
Aglio Database Solutions
San Francisco