Tim,

First, I have to say that I really like the support in EDB for the collate
clause related to character string types.

Playing with the collate clause, I've seen a couple of problems:

1) Collation name and identifier

create table test
(
 col1 integer,
 col2 varchar(10) collate enu,
 col3 varchar(10) collate enu_ci
)

Error:
ElevateDB Error #700 An error was found in the statement at line 5 and
column 28
(ElevateDB Error #401 The collation enu_ci does not exist)

It seems like the collation identifier is partly case-sensitive, collate
enu_CI does work

Now, inserting some data:

insert into test values (1,'A','A');
insert into test values (2,'a','a');
insert into test values (3,'A','A');

2) Comparison

select *
from test
where col2 collate enu_CI = 'a'

works fine.

select *
from test
where col2 = 'a' collate enu_CI

chokes on the collate clause.

3) Sort key specification

select *
from test
order by col2 collate enu_CI

The collate clause seems to be ignored. In fact, EDB doesn't complain about
an invalid collation name:

select *
from test
order by col2 collate invalid_collation_name

Ole Willy Tuv

Hello my dear friend Ole,

> First, I have to say that I really like the support in EDB for the
> collate clause related to character string types.
>
> Playing with the collate clause, I've seen a couple of problems:

Emm, i'm just curious. Do you have made an application doing all
these SQL tests or they are just plain SQL text files in a folder
in your i guess big collection ?

Thank you

--
Charalabos Michael - [Creation Power] - http://www.creationpower.gr

Ole,

<< It seems like the collation identifier is partly case-sensitive, collate
enu_CI does work >>

Yes, the CI, etc. extensions are case-sensitive.  I'll fix it.

<< select *
from test
where col2 = 'a' collate enu_CI

chokes on the collate clause. >>

Currently only columns can be forced to a particular collation.  General
expressions cannot.  Also, just to head you off at the pass - EDB does not
follow the collation coercibility rules in the standard.  It always uses the
left-hand side of the expression to determine the collation to be used for
the comparison.

<< 3) Sort key specification >>

Yep, it's picking up the collation reference for the column reference
instead of the entire ORDER BY expression.  It's now fixed.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Currently only columns can be forced to a particular collation.  General
expressions cannot. >>

Fair enough.

<< Also, just to head you off at the pass - EDB does not follow the
collation coercibility rules in the standard.  It always uses the left-hand
side of the expression to determine the collation to be used for the
comparison. >>

No problem.

Ole Willy Tuv

Hi Mihail!

<< Emm, i'm just curious. Do you have made an application doing all these
SQL tests or they are just plain SQL text files in a folder in your i guess
big collection ? >>

No, I just make the test cases as I go along with my testing/evaluation,
mostly based upon how I expect the syntax and clauses to work

Regards,
Ole

Hello Ole,

> No, I just make the test cases as I go along with my testing/evaluation,
> mostly based upon how I expect the syntax and clauses to work

Aha ... As i can see have a big collection of these test cases.

--
Charalabos Michael - [Creation Power] - http://www.creationpower.gr