db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein Grovlen - Sun Norway <Oystein.Grov...@sun.com>
Subject Re: Functions in GROUP BY expressions? (related to DERBY-883)
Date Wed, 15 Nov 2006 10:21:26 GMT
Yip Ng wrote:
 > On 11/14/06, *Oystein Grovlen - Sun Norway* <Oystein.Grovlen@sun.com
 > <mailto:Oystein.Grovlen@sun.com>> wrote:
 >     Just some late follow-up here.  As far as I can tell, it is not 
 >     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

I thought we had established that SIN was non-deterministic.  Are you
saying that SIN is deterministic in some contexts, but not in others?

 > 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

That was exactly my point.  It is putting the non-deterministic
functions both in the select list and the group-by clause that is the
problem, not non-deterministic functions in the group-by clause, per

 > 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.

If someone wants to group records in random ways, why should we
prevent them from doing so?

 > 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

The DB2 reference manual shows an example og how to get around this

            FROM EMPLOYEE
        ) AS EMPRAND

This made me try the following version of Army's original query on

SELECT sinus
   FROM ( SELECT sin(i) AS sinus
            FROM t
        ) AS sinust
GROUP BY sinus;

This works.  It is not clear to me why we should force people to write
people more complex queries than necessary to achieve what they want.

Øystein Grøvlen

View raw message