Mamta Satoor wrote:
Hi Dan,
You asked about how collation will be set for character expressions like
string literal, cast to character type of a character expression, trim,
concationation etc.
DTD will have an attribute called collation type and in 10.3, the
possible values for it will be -1 meaning UNKNOWN collation, 0 meaning
UCS_BASIC and 1 meaning TERRITORY_BASED. By default, DTD's will have the
collation type set to UNKNOWN. If the DTD is for a user table's CHAR
column, then DTD's collation will be set to TERRIOTRY_BASED/UCS_BASIC
depending on what was requested at database create time in the jdbc url.
This setting of collation will be done by DTD.setCollationType(int). If
the DTD is for a SYS schema table's CHAR column, then DTD's collation
will be set to UCS_BASIC.
I think there is a DTD associated with all the character expressions
like string literal, cast to character type of a character expression,
trim, concationation etc. And since the default collation type is
UNKNOWN, these character expressions will have their collation type as
UNKNOWN until they actually get used in a collation method. When they
get used in a collation method, their collation type will be determined
by the context in which they are. ie if the other operand of the
collation method has UCS_BASIC associated with them, then the character
expression's collation type in DTD will get set to UCS_BASIC and similar
logic if the other operand had TERRITORY_BASED collation type associated
with it.
I hope this answers your question. I will include this information on
the wiki page for DERBY-1478 so that everything is tracked in one
central location.
I'm not sure it's a simple as that. Consider this expression:
TRIM(x) < 'eee'
If x is a user column then I would expect the collation to be performed
using the collation for user columns, but if x was a system column I
would expect it to be performed using UCS_BASIC. I think it would be
much like nullability, the nullability of some operation of x is
dependent on the nullability of x, hence the collation of some operation
of x would be dependent on the collation of x.
Though of course, maybe the SQL standard defines it differently, it
would be good to know if it's defined by the standard or left as
implementation defined.
Dan.
