Skip site navigation (1) Skip section navigation (2)

Re: BUG #4173: Illogical error message with aggregates + order by

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chris West (Faux)" <pfx-psql(at)goeswhere(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4173: Illogical error message with aggregates + order by
Date: 2008-05-15 16:34:11
Message-ID: 6431.1210869251@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Chris West (Faux)" <pfx-psql(at)goeswhere(dot)com> writes:
> Consider the following SQL:

>   select count(bar) from foo order by bar;

> For this input, both postgresql-8.2* and -8.3.* output:

> ERROR:  column "foo.bar" must appear in the GROUP BY clause or be used in an
> aggregate function

It's complaining about the occurrence of "bar" in ORDER BY, which is not
within an aggregate function.

I'm not sure that we can do much with the wording of the message.
What would be more helpful here is a syntax error pointer, to keep
you from thinking that the occurrence of bar over in the count()
has got anything to do with it.

> I believe the SQL to be valid (although I could not provide a standard
> reference);

It is not.  I believe the relevant restriction in SQL99 is 14.1
syntax rule 18.f.i.2.A.I: "T shall not be a grouped table" if
you are trying to use an ORDER BY key that isn't the name of an
output column of the SELECT list.  The point here really is that
there is no well-defined value of bar to order the single output row
with.  The fact that the ordering is pretty much a no-op doesn't
excuse a semantically nonsensical ORDER BY specification.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-05-15 16:37:42
Subject: Re: mingw compile error
Previous:From: Devrim GÜNDÜZDate: 2008-05-15 16:30:31
Subject: Re: problem in installing pgsql-8.3.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group