Sorry, I'm still not seeing how you are using the sections of the
standard you quote to state that for:
NAME = 'Fred'
that the collation type of NAME is picked instead of the collation type
of 'Fred'. If I could understand how you are using those rules I could
then see how it applies in other situations, but I'm lost at the moment.
I think this bit you quote from *Syntax Rule* 9.13
9.13 Collation determination [...] e) Otherwise, every operand
whose collation derivation is implicit shall have the same
declared type collation IDTC and the collation to be used is IDTC.
is stating that all the implicit types involved in the collation must be
of the same type if collation is too succeed, otherwise it is a syntax
error. I don't think it is saying that all implicit types automatically
have the same collation (which one for example?).
[Note that this is only true if rules a)-d) did not apply which is the
case for NAME = 'Fred'.]
Thanks,
Dan.
Bernt M. Johnsen wrote:
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.
