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 23:52:25 GMT
Sorry, I'm still not seeing how you are using the sections of the 
standard you quote to state that for:

  NAME = 'Fred'

that the collation type of NAME is picked instead of the collation type 
of 'Fred'. If I could understand how you are using those rules I could 
then see how it applies in other situations, but I'm lost at the moment.


I think this bit you quote from *Syntax Rule* 9.13

>     9.13 Collation determination [...]  e) Otherwise, every operand
>     whose collation derivation is implicit shall have the same
>     declared type collation IDTC and the collation to be used is IDTC.

is stating that all the implicit types involved in the collation must be 
of the same type if collation is too succeed, otherwise it is a syntax 
error. I don't think it is saying that all implicit types automatically 
have the same collation (which one for example?).
[Note that this is only true if rules a)-d) did not apply which is the 
case for NAME = 'Fred'.]

Thanks,
Dan.


Bernt M. Johnsen wrote:
>>>>>>>>>>>>> Daniel John Debrunner wrote (2007-06-06
08:54:13):
>> 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.
>>
> You're right. I forgot to quote 4.2.2 which is important in this
> context:
> 
>     The comparison of two character string expressions depends on the
>     collation used for the comparison (see Subclause 9.13, $(B!H(BCollation
>     determination$(B!I(B).
> 
> and then you apply the quoted 9.13.
> 
>> Thanks for adding to the discussion, it's really useful for more eyes to 
>> be on this.
> 
> 


Mime
View raw message