Dear Wiki user,
You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.
The following page has been changed by MamtaSatoor:
http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478
The comment on the change is:
The paraphrasing of SQL spec was incorrect for aggregate operators.
------------------------------------------------------------------------------
10)CURRENT ISOLATION, CURRENT SCHEMA and CURRENT SQLID seem to be Derby specific functions,
I didn't find them in the SQL spec. But in order to match their behavior with the other functions
covered in 9) above, their return character string type's collation will be the collation
of character set SQL_IDENTIFIER. The collation derivation will be implicit.
- 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.
+ 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 collation derivation of IMPLICIT and same collation
type is associated with them, then the collation type of resultant character string type will
be same and it's collation derivation will be IMPLICIT. But if this criteria for equality
is not satisfied, then the resultant character string type will have collation derivation
of NONE. For CASE, NULLIF and COALESCE, this behavior is implemented in ValueNodeList:getDominantTypeServices()
and for CONCAT, the behavior is implemented in ConcatenationOperatorNode:resolveConcatOperation().
+
12)And last but not least, the collation methods will follow the rules defined by SQL spec
in Section 9.13 Collation determination Syntax Rules 2 and 3e. In other words, at least one
operand shall have a declared type collation (that means if the comparison is sysChar1|userChar1
> sysChar2|userChar2, then such a comparison will fail because both sides of > operator
have collation derivation of none after applying the rules of 10 above) AND every operand
whose collation derivation is implicit shall have the same declared type collation (that means
if the comparison is sysChar1 > userChar1WithTerritoryBasedCollation, then such a comparison
will fail because left operand has collation derivation as implicit and collation type as
UCS_BASIC and the right operand has collation derivation implicit and collation type as TERRITORY_BASED)