Commented by: @pavel-zotov

Adriano (Philippe ?) - can you please show proper usage of this collation ?

I've tried this:

-- (drop dependencies if any)
recreate table test(id int);
commit;

set term ^;
execute block as
begin
begin execute statement 'drop collation coll_fr'; when any do begin end end
begin execute statement 'drop sequence g'; when any do begin end end
end
^set term ;^
commit;
----------------------

create collation coll_fr for iso8859_1 from external ('FR_FR') case insensitive accent insensitive;
commit;
create sequence g;

recreate table test(id int, cf varchar(10) collate coll_fr);
commit;

recreate table noac(id int, nf varchar(10) collate coll_fr);
commit;

show collation coll_fr;
show table test;
show table noac;

insert into test(id, cf) values( gen_id(g,1), 'ç');
insert into test(id, cf) values( gen_id(g,1), 'é');
insert into test(id, cf) values( gen_id(g,1), 'à');
insert into test(id, cf) values( gen_id(g,1), 'è');
insert into test(id, cf) values( gen_id(g,1), 'ù');
insert into test(id, cf) values( gen_id(g,1), 'ë');
insert into test(id, cf) values( gen_id(g,1), 'ï');
insert into test(id, cf) values( gen_id(g,1), 'ü');
insert into test(id, cf) values( gen_id(g,1), 'â');
insert into test(id, cf) values( gen_id(g,1), 'ê');
insert into test(id, cf) values( gen_id(g,1), 'î');
insert into test(id, cf) values( gen_id(g,1), 'û');
insert into test(id, cf) values( gen_id(g,1), 'ô');
commit;

insert into noac(id, nf) values( gen_id(g,1), 'A');
insert into noac(id, nf) values( gen_id(g,1), 'C');
insert into noac(id, nf) values( gen_id(g,1), 'E');
insert into noac(id, nf) values( gen_id(g,1), 'I');
insert into noac(id, nf) values( gen_id(g,1), 'O');
insert into noac(id, nf) values( gen_id(g,1), 'U');
commit;

select http://n.id n_id, http://n.nf, http://t.cf, http://t.id t_id
from noac n
left join test t on http://n.nf = http://t.cf ; --left join test t on http://n.nf is not distinct from http://t.cf ; -- the same

-- and got something strange:

COLL_FR, CHARACTER SET ISO8859_1, FROM EXTERNAL ('FR_FR'), CASE INSENSITIVE, ACCENT INSENSITIVE

ID INTEGER Nullable
CF VARCHAR(10) CHARACTER SET ISO8859_1 Nullable
COLLATE COLL_FR
ID INTEGER Nullable
NF VARCHAR(10) CHARACTER SET ISO8859_1 Nullable
COLLATE COLL_FR

    N\_ID NF         CF                 T\_ID 

============ ========== ========== ============
14 A ç 1
14 A é 2
14 A è 4
14 A ë 6
14 A ï 7
14 A â 9
14 A î 11
14 A û 12
14 A ô 13
15 C <null> <null>
16 E <null> <null>
17 I <null> <null>
18 O <null> <null>
19 U <null> <null>

How comparison of 'A' (upper-cased, no-accent) and other vowels ('E', 'I', 'O', 'U' ) or 'C' with cedilla accent, can return TRUE ?

Commented by: @asfernandes

What version? I do not see this with trunk.

Commented by: @asfernandes

No problem in 2.5 too.

Commented by: @pavel-zotov

I'm trying on WI-T3.0.0.31840 (both database and client on Windows XP host).
Please see in attached .zip - this is screenshot from my console (cmd.exe ==> chcp 28591 ==> isql ....)

PS. http://en.wikipedia.org/wiki/ISO/IEC_8859-1 -- states that "Code page 28591 a.k.a. Windows-28591 is the actual ISO-8859-1 codepage".

Commented by: @pavel-zotov

I've just tried on LI-T3.0.0.31827 with:

$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

-- and see the SAME result as on Windows.

Slightly modified test:

set names iso8859_1;
create database '192.168.0.220/3333:/var/db/fb30/c1366.fdb' default character set iso8859_1;

show version;
show database;

set list on;
select c.rdb$character_set_name as connection_cset, r.rdb$character_set_name as db_default_cset
from mon$attachments a
join rdb$character_sets c on a.mon$character_set_id = c.rdb$character_set_id
cross join rdb$database r where a.mon$attachment_id=current_connection;
set list off;

recreate table test(id int);
commit;

set term ^;
execute block as
begin
begin execute statement 'drop collation coll_fr'; when any do begin end end
begin execute statement 'drop sequence g'; when any do begin end end
end
^set term ;^
commit;

create collation coll_fr for iso8859_1 from external ('FR_FR') case insensitive accent insensitive;
commit;
create sequence g;

recreate table test(id int, cf varchar(10) collate coll_fr);
commit;

recreate table noac(id int, nf varchar(10) collate coll_fr);
commit;

show collation coll_fr;
show table test;
show table noac;

insert into test(id, cf) values( gen_id(g,1), 'ç');
insert into test(id, cf) values( gen_id(g,1), 'é');
insert into test(id, cf) values( gen_id(g,1), 'à');
insert into test(id, cf) values( gen_id(g,1), 'è');
insert into test(id, cf) values( gen_id(g,1), 'ù');
insert into test(id, cf) values( gen_id(g,1), 'ë');
insert into test(id, cf) values( gen_id(g,1), 'ï');
insert into test(id, cf) values( gen_id(g,1), 'ü');
insert into test(id, cf) values( gen_id(g,1), 'â');
insert into test(id, cf) values( gen_id(g,1), 'ê');
insert into test(id, cf) values( gen_id(g,1), 'î');
insert into test(id, cf) values( gen_id(g,1), 'û');
insert into test(id, cf) values( gen_id(g,1), 'ô');
commit;

insert into noac(id, nf) values( gen_id(g,1), 'A');
insert into noac(id, nf) values( gen_id(g,1), 'C');
insert into noac(id, nf) values( gen_id(g,1), 'E');
insert into noac(id, nf) values( gen_id(g,1), 'I');
insert into noac(id, nf) values( gen_id(g,1), 'O');
insert into noac(id, nf) values( gen_id(g,1), 'U');
commit;

select http://n.id n_id, http://n.nf, http://t.cf, http://t.id t_id
from noac n
left join test t on http://n.nf is not distinct from http://t.cf ;

Result (screenshot from Putty console) see in attached .zip.

Commented by: @asfernandes

Try puting the commands in a file saved with ISO8859-1 charset and 'input' it in ISQL.

Commented by: @asfernandes

It's obviously in your Windows screenshot that you're having a conversion problem, not a comparation problem.

Commented by: @pavel-zotov

> Try puting the commands in a file saved with ISO8859-1 charset and 'input' it in ISQL.

All works fine, thanks. Script should be encoded in iso8859-1 rather than in utf8.