From: Josh Berkus <josh@agliodbs.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dan Langille <dan@langille.org>
Cc: pgsql-bugs@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 7.3 GROUP BY differs from 7.2
Date: Sun, 23 Feb 2003 12:19:08 -0800
Message-ID: <200302231219.08727.josh@agliodbs.com> (raw)
In-Reply-To: <20030222091532.F73514-100000@megazone23.bigpanda.com>
References: <20030222091532.F73514-100000@megazone23.bigpanda.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, sszabo@megazone23.bigpanda.com, tgl@sss.pgh.pa.us, dan@langille.org, pgsql-bugs@postgresql.org
Subject: Re: [SQL] 7.3 GROUP BY differs from 7.2
In-Reply-To: <200302231219.08727.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox