db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manish Khettry" <manish.khet...@gmail.com>
Subject Re: Functions in GROUP BY expressions? (related to DERBY-883)
Date Tue, 14 Nov 2006 23:50:20 GMT
Hmm strange. I am not sure why this

   select avg(i) from t group by sin(i)

works but not this,

  select avg(i), sin(i) from test group by sin(i);

I think its a bug-- both should be disallowed.

m

On 11/14/06, Oystein Grovlen - Sun Norway <Oystein.Grovlen@sun.com> wrote:
>
> Yip Ng wrote:
>
> > For SIN, COS, TAN functions,  they are also considered deterministics.
> > However, they do not work currently in Derby with group by expression as
> > they are mapped to  JavaToSQLValueNode and such nodes always return
> > false in isEquivalent() method, so I think this is an implementation
> > restriction for those built-in functions.
>
> Just some late follow-up here.  As far as I can tell, it is not strictly
> correct to say that non-deterministic functions does not work with group
> by expressions.  What does not work is to use a non-deterministic
> function in the select list of a statement when grouping on the same
> function.  However, the following query works:
>
>         select avg(i) from t group by sin(i)
>
> I assume that it is the check for equivalence between the select list
> and the group by list that requires deterministic functions.  (This may
> have been clear to others when this was discussed, but at least not to
> me.)
>
> --
> Øystein
>

Mime
View raw message