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
<snip>
ij> select sin(i) from t1 group by sin(i);
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
_expression_. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
</snip>
and that the current implementation does not allow this due to JavaToSQLValueNode's
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
Regards,
Yip Ng
