>>>>>>>>>>>> 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
signature.asc
Description: Digital signature
