db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Collation and string literals limitation in SQL standard?
Date Wed, 06 Jun 2007 19:01:54 GMT
>>>>>>>>>>>> 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.


-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message