Re: Functional dependencies and GROUP BY

On tis, 2010-06-08 at 09:59 +0900, Hitoshi Harada wrote:
> > Also, when a column is compared with a constant, it can appear
> > ungrouped:
> >
> > SELECT x, y FROM tab2 WHERE y = 5 GROUP BY x;
>
> I don't see why it should be allowed. I see the insist that y must be
> unique value so it is ok to be ungrouped but the point of discussion
> is far from that; Semantically y is not grouping key.

I'm not sure what your argument is. If y is uniquely determined within
each group, then it's OK for it to be ungrouped. What other criteria do
you have in mind for determining that instead? It looks like you are
going by aesthetics. ;-)

> In addition, what if y is implicitly a constant? For example,
>
> SELECT x, y FROM tab2 WHERE y = a AND a = 5 GROUP BY x;
>
> or there should be more complicated example including JOIN cases. I
> don't believe we can detect all of such cases. If the simple case is
> allowed, users don't understand why the complicated case doesn't allow
> sometimes. So it'll not be consistent.

Yes, as I said, my implementation is incomplete in the sense that it
only recognizes some functional dependencies. To recognize the sort of
thing you show, you would need some kind of complex deduction or proof
engine, and that doesn't seem worthwhile, at least for me, at this
point.