Re: Insufficient description in collation mismatch error

Thom Brown <thom(at)linux(dot)com> writes:
> I tried applying a collation to a GROUP BY clause without applying the
> collation to the corresponding column in the SELECT clause.

> postgres=# SELECT things, count(*) FROM stuff GROUP BY things COLLATE "C";
> ERROR: column "stuff.things" must appear in the GROUP BY clause or be
> used in an aggregate function
> LINE 1: SELECT things, count(*) FROM stuff GROUP BY things COLLATE "...

> Firstly, does it even make sense for a GROUP BY clause to accept COLLATE?

Probably, or at least I'm hesitant to hard-wire a restriction against
it. The question is isomorphic to whether you believe that different
collations can have different equality semantics. You'd want that for
instance if you wanted a collation to be able to implement
case-insensitive comparisons. The SQL committee seem to believe that
that is possible, because they take the trouble to specify that
foreign-key comparisons are done using the referenced not referencing
column's collation; there'd be no need for that verbiage if it couldn't
matter. But there are a number of places in our existing code that
would need to be improved before we could support such a thing; in
general I'd have to say the code is pretty schizophrenic on the point.

> Even if it does, this error message doesn't explain the problem, being
> that the column with the necessary collation doesn't appear in the
> SELECT.

This isn't a new problem particularly; it happens whenever a GROUP BY
item isn't just a simple variable. For example

regression=# select f1 from int4_tbl group by abs(f1);
ERROR: column "int4_tbl.f1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select f1 from int4_tbl group by abs(f1);
^

I agree this isn't terribly user-friendly, but it's not real clear to me
how to do better.

regards, tom lane