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 Fri, 08 Jun 2007 22:04:42 GMT
Bernt M. Johnsen wrote:
> I have to admint that my interpretation of the standard was somewhat
> biased with wishful thinking. Going through the standards together
> with a competent collegue here in Trondheim (he has actually
> implemented collation in a db), we concluded the following:
> Given your example with the table T
>       create table T(NAME CHAR(10) COLLATE X);
> and the boolean expression
>       NAME = 'Fred'
> 1) NAME has collation derivation implicit, and thus has collation X in
>    this expression.
> 2) 'Fred' has collation derivation implicit and the declared type
>    collation of 'Fred' is "the character set collation". (5.3 Syntax
>    Rule 15).  
> 3) The character set of 'Fred' is "the character set of the SQL-client
>    module" (5.3 Syntax Rule 14b).
> 4) The module has a Schema and the Schema has a character set and the
>    character set has a collation which thus becomes the collation for
>    'Fred' which in this case is not collation X.
> According to this we should get a syntax error according to 9.13
> Syntax Rule 3e.
> If we didn't miss some details, we end up where Dan started. The
> problem with this, is that 
> 1) It's not user friendly
> 2) It's not intuitive
> 3) And, in SQL-99, string literals had collation derivation
>    *coercible*, which would give 'Fred' in this context collation X
>    and the expression was then legal. It puzzles me that after
>    coercible was removed, I can't find anything in SQL-2003 which
>    replaces this semantics and makes the expression legal.
> One could also argue that it was natural that string literals got
> their collation the same way that NULL got it's type in expressions,
> but I find no such thing specified in the standard.
> It also seems odd that databases which implemented this according to
> SQL-99 and then moved to SQL-2003 would break upwards compatability in
> this respect. It would be nice if someone knew a person in the SQL
> standardization committee and could pass this problem along to them,
> at least to get a statement that we have interpreted the standard
> right (or wrong).

Thanks for looking into this in detail Bernt.

I sent links to this conversation off to some SQL standard experts, they 
gave me back some info which I have yet to go through fully. The quick 
summary would be that these types of issues were raised back in 2001(?) 
and the current 2003 standard is the result of addressing those 
concerns! Thus there are no known problems ...

The docs are hard to read as they are corrections to some draft of the 
standard which result in the 2003 standard. However there are a couple 
of informative notes which may help out, I'll spend more time reading.


View raw message