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 Thu, 07 Jun 2007 11:47:14 GMT
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).

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

View raw message