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
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.

Mime
View raw message