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.
Dan.
