> -----Original Message-----
> From: Jim Starkey [mailto:jas@...]
> Sent: Wednesday, February 21, 2001 1:03 PM
> To: IB-Architect@yahoogroups.com
> Subject: [IB-Architect] Collation Sequences
>
>
> Does anybody know what the official rules are for
> comparisons of string of different collating sequences?
> Or comparing a string with a collating seqeuence with
> string without an explicit collating sequence (for example,
> a string literal)?

The official SQL rules are 6 pages of incredibly dense mathmatical
symbolism
that once upon a time gave me no end of headaches.

As I recall my ability to figure it out, it was approximately as
follows:

<exp1> <relop> <exp2>

If <exp*> contains a COLLATE <x> clause, it has an explicit collation
of <x>.
If <exp*> is a simple column reference, it has the explicit collation
of that column's definition.
If <exp*> is a 'literal'. It has the implicit collation of the
default collation of the character set.
If <exp*> is an expression (UPPER(x), etc.), it has an implicit
collation -- which is either
the collation of the terms in the expression, or the default for the
charset (can't exactly recall).

Explicit collations always trump explicit ones.
If two explicit exist, and don't match, it's an error.
I suspect that two non-matching implicit is also an error.

eg:
german_field = 'literal' collation of german_field.
upper(german_field) = 'literal' Not sure for SQL rules, but
should be collation of german_field.
german_field = dutch_field Error.
german_field = dutch_field COLLATE german -- German.
german_field = dutch_field COLLATE dutch -- Error?
'literal'= 'literal' Charset of 'literal'
_CS1 'literal'= _CS2 'literal' If default collation for _CS1
& _CS2 is the same,
then OK, otherwise error.

>
> Also, is the collation clause on a comparison operator a
> modifier on a value or the comparison operator itself?

I've asked myself that several times. Since it is legal to put a
collate clause on either side
of the Relop, I suspect it's a modifier on the value. And then
there's the 6 pages of rules
to decide how the relop resolves differences between the two
expressions.

The v4.0 implementation of internationalization played fairly loose on
the rules.
In particular, it allows
f1 COLLATE One = f2 COLLATE Two
as legal. Internally it picks the higher? Lower? numbered one, and
uses that for the
equality operator. This allowed easier migration from v3.3, but
wasn't correct.

Unfortunetly, fixing all these sql'ism's in a later release never
occurred, and we still have
the v4.0 implementation in v6.0

>
> And while we're at it, what is/should be the collation
> sequence of a sql case expression of different collation
> sequences?

I'd follow the "rules" that UNION & concatentate imply. At least in
Firebird.
SELECT A ...
UNION
SELECT B ...

Result is datatype of A.

A || B ===> B is converted to A's datatype (charset & collation).
This likely isn't "right", and certainly non-optimal in some cases.
eg:
'Dear Mr. ' || aname || ':' --- should likely be in
charset/collation of aname. Well, at least the charset of aname....

>
> Mr. Schnepper? Ms. Brown?
>
> I will accept answers in three parts:
>
> 1. What does the standard say?

6 pages of BS. (Not much help, I know...) <grin>

> 2. What does Firebird do?

As above.

> 3. With the advantage of hindsight, what is
> the right answer?

If there's any ambiguity, post an error. I like picky C compilers, I
like picky SQL parsers.
I don't think functions like UPPER(x) should "lose" the collation of
x. Ditto things like
x || y -- except when collation(x) != collation(y).

>
> Jim Starkey
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
>
>
>
>

[Non-text portions of this message have been removed]