You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Daniel John Debrunner <dj...@apache.org> on 2007/06/05 00:33:21 UTC

Collation and string literals limitation in SQL standard?

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

It gets a little stranger if a comparison expression is used within the 
create table:

  create table T(TYPE CHAR(10) COLLATE X CHECK TYPE IN ('CAR', 'PLANE', 
'BOAT');

Now the create table will fail.

Both of these could be made to work with a COLLATE clause, but it 
becomes especially strange in the CHECK clause, as the COLLATE clause 
must now appear multiple times (I think).

  create table T(TYPE CHAR(10) COLLATE X CHECK TYPE IN ('CAR' COLLATE X, 
'PLANE' COLLATE X, 'BOAT' COLLATE X);

In both of these cases it seems (to me) that what the language really 
intends is that collation to be used is that of the column reference.

Note that even without multiple user collations, Derby will have this 
problem for queries that system columns and user schemas, e.g. 
sys.systables.TABLENAME = 'MYTABLE' will fail if compiled in a user schema.

Interestingly both MySQL and Microsoft have solved this problem by 
adding additional levels to the derivation concept. SQL Standard 
supports three levels: NONE, IMPLICIT and EXPLICIT.

Microsoft has added one: Coercible-default between implicit and none.

http://msdn2.microsoft.com/en-us/library/ms179886.aspx

and string literals map to coercible-default, thus in the above examples 
the column's collation will be used.

MySQL instead has a concept of "coercibility" with six levels, 0-5. This 
is claimed to be SQL standard but seems to be from an earlier version of 
the standard. I found another reference to it in some SQL book.

http://doc.51windows.net/mysql/?url=/mysql/ch10s05.html
(section 10.5.4)

Again string literals are at a different level to column references 
leading to column collation being used.


Does anyone know of any correction in the SQL standard regarding this 
issue? I haven't been able to find one.

I'm just thinking about the future and trying to ensure that anyone who 
implements multiple user collations for Derby won't have to break 
backwards compatibility to ensure rational behaviour.

Thanks,
Dan.


Re: Collation and string literals limitation in SQL standard?

Posted by Daniel John Debrunner <dj...@apache.org>.
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.

Re: Collation and string literals limitation in SQL standard?

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
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

Re: Collation and string literals limitation in SQL standard?

Posted by Daniel John Debrunner <dj...@apache.org>.
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.
> 
> 


Re: Collation and string literals limitation in SQL standard?

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> 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.


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

Re: Collation and string literals limitation in SQL standard?

Posted by Daniel John Debrunner <dj...@apache.org>.
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.

Thanks for adding to the discussion, it's really useful for more eyes to 
be on this.
Dan.

Re: Collation and string literals limitation in SQL standard?

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> 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.

    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.

That is, when the collation is implicit, it should be that of the
derived type. 

> It gets a little stranger if a comparison expression is used within the 
> create table:
> 
>  create table T(TYPE CHAR(10) COLLATE X CHECK TYPE IN ('CAR', 'PLANE', 
> 'BOAT');
> 
> Now the create table will fail.

Without having checked what happens with the derived type exactly for
this example, I think it is, due to 3.1.6.7 and 9.13, safe to assume
that the collation of 'CAR' etc here should be X.

> Both of these could be made to work with a COLLATE clause, but it 
> becomes especially strange in the CHECK clause, as the COLLATE clause 
> must now appear multiple times (I think).
> 
>  create table T(TYPE CHAR(10) COLLATE X CHECK TYPE IN ('CAR' COLLATE X, 
> 'PLANE' COLLATE X, 'BOAT' COLLATE X);
> 
> In both of these cases it seems (to me) that what the language really 
> intends is that collation to be used is that of the column reference.
> 
> Note that even without multiple user collations, Derby will have this 
> problem for queries that system columns and user schemas, e.g. 
> sys.systables.TABLENAME = 'MYTABLE' will fail if compiled in a user schema.
> 
> Interestingly both MySQL and Microsoft have solved this problem by 
> adding additional levels to the derivation concept. SQL Standard 
> supports three levels: NONE, IMPLICIT and EXPLICIT.
> 
> Microsoft has added one: Coercible-default between implicit and none.
> 
> http://msdn2.microsoft.com/en-us/library/ms179886.aspx
> 
> and string literals map to coercible-default, thus in the above examples 
> the column's collation will be used.
> 
> MySQL instead has a concept of "coercibility" with six levels, 0-5. This 
> is claimed to be SQL standard but seems to be from an earlier version of 
> the standard. I found another reference to it in some SQL book.
> 
> http://doc.51windows.net/mysql/?url=/mysql/ch10s05.html
> (section 10.5.4)
> 
> Again string literals are at a different level to column references 
> leading to column collation being used.
> 
> 
> Does anyone know of any correction in the SQL standard regarding this 
> issue? I haven't been able to find one.
> 
> I'm just thinking about the future and trying to ensure that anyone who 
> implements multiple user collations for Derby won't have to break 
> backwards compatibility to ensure rational behaviour.
> 
> Thanks,
> Dan.
> 

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

Re: Collation and string literals limitation in SQL standard?

Posted by Mike Matrigali <mi...@sbcglobal.net>.

Daniel John Debrunner wrote:
> 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).
> 
> It gets a little stranger if a comparison expression is used within the 
> create table:
> 
>  create table T(TYPE CHAR(10) COLLATE X CHECK TYPE IN ('CAR', 'PLANE', 
> 'BOAT');
> 
> Now the create table will fail.
> 
> Both of these could be made to work with a COLLATE clause, but it 
> becomes especially strange in the CHECK clause, as the COLLATE clause 
> must now appear multiple times (I think).
> 
>  create table T(TYPE CHAR(10) COLLATE X CHECK TYPE IN ('CAR' COLLATE X, 
> 'PLANE' COLLATE X, 'BOAT' COLLATE X);
> 
> In both of these cases it seems (to me) that what the language really 
> intends is that collation to be used is that of the column reference.
> 
> Note that even without multiple user collations, Derby will have this 
> problem for queries that system columns and user schemas, e.g. 
> sys.systables.TABLENAME = 'MYTABLE' will fail if compiled in a user schema.
> 
> Interestingly both MySQL and Microsoft have solved this problem by 
> adding additional levels to the derivation concept. SQL Standard 
> supports three levels: NONE, IMPLICIT and EXPLICIT.
> 
> Microsoft has added one: Coercible-default between implicit and none.
> 
> http://msdn2.microsoft.com/en-us/library/ms179886.aspx
> 
> and string literals map to coercible-default, thus in the above examples 
> the column's collation will be used.
> 
> MySQL instead has a concept of "coercibility" with six levels, 0-5. This 
> is claimed to be SQL standard but seems to be from an earlier version of 
> the standard. I found another reference to it in some SQL book.
> 
> http://doc.51windows.net/mysql/?url=/mysql/ch10s05.html
> (section 10.5.4)
> 
> Again string literals are at a different level to column references 
> leading to column collation being used.
I don't know any standard reference.  Both the mysql and oracle rules 
seem more user friendly with regard at least to comparison between 
constants and implicit collation references (columns being the case
I usually think of affecting the most usage).
> 
> 
> Does anyone know of any correction in the SQL standard regarding this 
> issue? I haven't been able to find one.
> 
> I'm just thinking about the future and trying to ensure that anyone who 
> implements multiple user collations for Derby won't have to break 
> backwards compatibility to ensure rational behaviour.
> 
> Thanks,
> Dan.
> 
>