Guys,
SQL spec aside, thinking about this from a strictly implementation/user point
of view:
(an keeping in mind that I think it's very important that we work out the
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 weekend
of relational calculus someone (not me) could define the cases where it is OK
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 spend
an hour or more hunting for the wrong error.
I propose that, should we decide not to change the behaviour of the parser,
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] must
be GROUPed or used in an aggregate function" and my GROUP BY query won't run,
even though all of the columns are in the GROUP BY clause. This query may
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 clause
than in the GROUP BY clause, causing the parser to be confused about what you
really mean. For example, you may be referring to a column by its simple
column name ("element_id") in the SELECT clause, and by its table-qualified
name ("table1.element_id") in the GROUP BY clause, or you may be using an
alias in one place but not the other. Please make sure that all columns in
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
backward compatibility.
Thoughts?
--
Josh Berkus
Aglio Database Solutions
San Francisco