Yip Ng wrote:
For SIN, COS, TAN functions, they are also considered deterministics.
However, they do not work currently in Derby with group by expression as
they are mapped to JavaToSQLValueNode and such nodes always return
false in isEquivalent() method, so I think this is an implementation
restriction for those built-in functions.
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. (This may
have been clear to others when this was discussed, but at least not to me.)
--
Øystein
