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".
My understanding is that both "substr" and "sin" are considered "functions" in
Derby--at least, they both show up in the "Built-in Functions" list for the
documentation. If that's the case, though, then I would expect that they *both*
be non-deterministic--but in actuality, "substr" seems to work.
I can see that "sin(i)" gets mapped to a JavaToSQLValueNode and such nodes
always return "false" for the "isEquivalent()" method, so that part makes sense
to me. And "substr()" gets mapped to a TernaryOperatorNode where isEquivalent()
can potentially return "true". So that explains why the two behave differently.
But is this difference accidental or deliberate? If it's deliberate, why is
"substr" allowed but "sin" rejected? Is there something about string functions
that makes them deterministic where a function like "sin" is not?
If it's true that we allow some functions in a GROUP BY clause but not others, I
think that's probably something worth including in whatever documentation is
written for DERBY-883. Assuming, of course, that I'm not just missing something
obvious.
Sorry if I'm being dense here...
Army
