Re: Requesting advanced Group By support

Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> On 10/09/2018 03:10 PM, Arun Kumar wrote:
>> *SELECT a.sno,b.sno,a.name,b.location FROM Name AS a JOIN Location AS b
>> ON a.sno=b.sno GROUP BY a.sno,b.location *
>>
>> In this case, a.sno is a primary key so no need to include a.name in
>> GROUP By as it would be identified by the primary key and then for b.sno
>> which is again equated with a.sno (primary key) so no need to add this
>> as well but for b.location, we need to add it in GROUP BY or we should
>> use any aggregate function over this column to avoid error.

> So, which part of this supposedly does not work in PostgreSQL?

The part where it infers that b.sno is unique based solely on it having
been equated to a.sno.

I'm not sure whether the SQL spec's definition of functional dependencies
includes such a proof rule, but I'm not very excited about adding one to
PG. It's likely of limited use, seeing that this is the first time I can
recall anyone asking for it; and it'd create dependency problems that we
don't have today, because validity of the query would depend on the
existence of a btree operator class from which we could infer that
uniqueness of a.sno implies uniqueness of b.sno. We have enough problems
arising from the existing case of validity of the query depending on the
existence of a primary key. Also, a primary key is at least a
well-defined dependency (there can be only one); but since an equality
operator could belong to multiple opclasses, it's not very clear which
one the query would get marked as depending on.

In short: the cost/benefit ratio of this optimization looks pretty bad.

regards, tom lane