Many thanks to all who took the time to provide clarifications in this thread.
At this point I think the only question I have remaining is with respect to
something Dan wrote in one of his replies, namely:
> the grouping by functions does not work because at the moment Derby
> assumes all functions are non-deterministic.
Note the use of the word "all".
I think what Dan meant was that all *user defined functions* defined via CREATE FUNCTION statement are assumed to be non-deterministic since users cannot declare a function as deterministic in the current implementation of Derby, so implicitly, they are non-deterministic.
For built-in functions such as SUBSTR, LTRIM and RTRIM, I believe they are deterministic since they always return the same result from successive invocations with identical input(s)
and they work as expected with group by _expression_.
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.
Perhaps it is a good idea to have documentation that describes the function determinism of all built-in functions in the reference manual.
Regards,
Yip Ng
