Re: Collation and string literals limitation in SQL standard?

[Daniel John Debrunner]

Bernt M. Johnsen wrote:
Daniel John Debrunner wrote (2007-06-04 15:33:21):
Following the SQL standard the collation derivation of a string literal 
and a column reference are both "implicit". This is what the current 
collation feature is implementing.

This leads to some interesting cases once Derby supports column specific 
collations.

Take:

create table T(NAME CHAR(10) COLLATE X);

Now the boolean expression in a query

   NAME = 'Fred'

will fail if the collation for 'Fred' is not X. That could exist with 
the current implementation (literals default to the collation of the 
current schema) or the original proposal (literals default to the 
database's collation).

A literal's collation is (as stated) implicit. But I interpret the
standard like this:

The derived type of 'Fred' in the expression

SELECT NAME FROM T WHERE NAME = 'Fred'

is the type of NAME which is CHAR(10) and since the collation
derivation is implicit, 'Fred' has the collation X.

This follows from the folling sections in SQL 2003:

    3.1.6.7 declared type (of an expression denoting a value or anything
    that can be referenced to denote a value, such as, for example, a
    parameter, column, or variable): The unique data type that is common
    to every value that might result from evaluation of that expression.

Can you explain more how you see 3.1.6.7 applying here? I read it as 
saying every value that could be produced by an expression must be a 
valid value for the data type of the expression. It doesn't seem to have 
any implications for setting the types of one expression based upon 
another expression.

I see NAME = 'Fred' as having three expressions:

 NAME  - type CHAR(10)
 'Fred' - type CHAR(4)
 NAME = 'FRED' - type BOOLEAN

Now 'Fred' must have a collation type as well as being derivation 
implicit because we know that this expression must work in SQL:

     'Fred' > 'Barney'

So I can't see how 3.1.6.7 leads to the collation of NAME being picked 
over the collation of 'Fred'. I agree that's the desired behaviour, but 
I just don't see it from 3.1.6.7.

Thanks for adding to the discussion, it's really useful for more eyes to 
be on this.
Dan.