db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: Collation and string literals limitation in SQL standard?
Date Wed, 06 Jun 2007 15:54:13 GMT
Bernt M. Johnsen wrote:
>>>>>>>>>>>>> Daniel John Debrunner wrote (2007-06-04
>> 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
> 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:
> 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 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 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

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

View raw message