Yip Ng wrote:
> On 11/14/06, *Oystein Grovlen - Sun Norway* <[EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]>> 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
