The collation wiki page has this for its "rule 11":
http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478
-------------------------------------------------------------------
11)Aggregate operators involving all character string type
operands(Concatenation (see 6.28SR5b), CASE, NULLIF, COALESCE) will
follow SQL spec Section 9.3 Data types of results of aggregations. In
other words, if all the operands have the same collation associated with
them, then the collation of result character string type will be same
and the collation derivation will be implicit. But if operands of
different collation types are involved, then the result character string
type will have collation derivation of none.
-------------------------------------------------------------------
The text above starting with "In other words, ..." doesn't exactly match
the definition in the SQL standard 9.3, which is what it is trying to
paraphrase. SQL standard says if every type has implicit derivation
*and* is of the same type, then the collation will be of that type with
derivation implicit, otherwise the derivation will be none.
I haven't looked at the code to see what is done in Derby, but wanted to
throw this out as a potential issue, either the code should be fixed if
wrong, or the wiki page clarified.
Dan.
[this is of course ignoring explicit derivation which is not yet supported].
