Re: asterisk (non)expansion in GROUP BY clause

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Apparently, you can write this (an attempt at a convenient workaround
> for lack of functional dependency tracking pre-9.1):

> SELECT pg_class.* FROM pg_class GROUP BY pg_class.*;

> It won't work:

> ERROR: 42803: column "pg_class.relname" must appear in the GROUP BY clause or be used in an aggregate function

I haven't traced through the code, but I think what is happening is that
the GROUP BY is interpreted like GROUP BY ROW(pg_class.x, pg_class.y, ...)
which doesn't guarantee uniqueness of the individual columns.

The star notation in the target list is interpreted differently: it
results in an explicit expansion into separate Var references. And then
those fail the grouping check since they're not forced unique by the
GROUP BY clause.

> Is there any rhyme or reason for this? I couldn't find anything about
> this in the documentation or in the SQL standard. I guess the whole
> thing is inconsistent all over the place; I'd just like to verify that
> the current behavior is somewhat intentional.

The behavior in the target list is mandated by SQL spec, for sure.
But I doubt that the spec defines the above GROUP BY syntax at all.

I wouldn't claim that the current behavior in GROUP BY is "intentional"
--- it's not a case that I ever thought about, anyway. Not sure how
practical it would be to change that.

regards, tom lane