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