db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng" <yipng...@gmail.com>
Subject Re: Functions in GROUP BY expressions? (related to DERBY-883)
Date Tue, 14 Nov 2006 16:44:28 GMT
On 11/14/06, Oystein Grovlen - Sun Norway <Oystein.Grovlen@sun.com> wrote:
> 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.

In your example, AVG and (its other aggregate friends) and SIN are
deterministic, so
select avg(i) from t group by sin(i) works expectedly.  I was referring to
Army's example
where the sin(i) is in the select list

ij> select sin(i) from t1 group by sin(i);
ERROR 42Y30: The SELECT list of a grouped query contains at least one
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.

and that the current implementation does not allow this due to

behavior in isEquivalent() method.  Hope I clear this up. =)

On a side note, I think it is awkward and perhaps wrong to group by with
non-deterministic functions as the
grouping will have unpredictable result, so they shouldn't be allow in the
group by clause in my opinion.
For a quick reference, I created a non-deterministic function named f_nd in
DB2 and attempt to group by
with this function and as I expected, it throws an error.  e.g.:

db2 => select avg(i) from t1 group by f_nd(i)
SQL0583N  The use of routine "S1.F_ND" is invalid because it is not
deterministic or has an external action.  SQLSTATE=42845

Yip Ng

View raw message