Re: Collation feature discussion

[Daniel John Debrunner]

Mamta Satoor wrote:
Before talking about functions, I think it will be better to first talk 
about string literals and their collation determination.
 
SQL spec section 5.3 <literal>, Syntax Rule 15) says "The declared type 
collation of a <character string literal> is the character set 
collation, and the collation derivation is implicit."
 
Based on this, when a string literal (collation type UNKNOWN) is getting 
used in a collation method with another operand as UCS_BASIC collation, 
then the collation type of string literal will be UCS_BASIC. Similar 
rule for operand with TERRITORY_BASED. In a case where, collation types 
of all the operands is UNKNOWN, at collation time, it can be assumed to 
be whatever is defined for user defined character columns. This will be 
similar to the example given by Rick for implicit collation type when 
talking about CAST ie
CREATE TABLE t1 (c11 char(1) default 'a') In this example, the collation 
type of DTD associated with 'a' will be implicitly whatever is defined 
at the database level for COLLATION.
 
Hope this answers the question about string literals.

Kind of, I looked up the definition of "collation derivation is 
implicit" in section 4.2.2 of the standard and at first reading it 
wasn't obvious to me what it meant.

I know I suggested the 'collation type UNKNOWN' but I hadn't looked into 
 the SQL standard in detail, and now I'm wondering if the UNKNOWN 
concept is a good idea. Since the SQL standard already defines a model 
for how collations are defined it might be wise to follow the required 
model and naming. Not sure what that would mean exactly, but it seems 
like each character expression can have a derivation of explicit, 
implicit or none. These may be better ways to carry state rather than 
unknown. Unless of course there's a clear mapping between unknown and 
the sql standard definition.

Dan.