Thread: asterisk (non)expansion in GROUP BY clause

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

But the whole thing is a bit confusing.  This works (of course):

SELECT pg_class FROM pg_class GROUP BY pg_class;

And this behaves equivalently, apparently:

SELECT pg_class FROM pg_class GROUP BY pg_class.*;

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.

Peter Eisentraut <peter_e@gmx.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