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

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

 > 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
restriction:

SELECT RANDID ,AVG(EDLEVEL ),AVG(SALARY )
   FROM ( SELECT EDLEVEL,SALARY,INTEGER(RAND()*5) AS RANDID
            FROM EMPLOYEE
        ) AS EMPRAND
GROUP BY RANDID

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

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

Mime
View raw message