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

"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