Analitico and all,

I found that if  I add the a extra field in the index like the pk and make the index unique it works fine.

Many of those collation problems seem to relate to unicode_ci and unicode_ci_ai,
and this relates to the usage of ICU functions.

FB seems to set collation strength properly only where it relates to unique index.
Otherwise the strength is left default, i.e. equivalent of collate unicode, for unicode_ci and unicode_ci_ai.
This is my guess after tested and observed many cases.

If a sorting was ordered by a single column with collate UNICODE,
the result could be shared with collate UNICODE_CI or UNICODE_CI_AI.
But when it comes to ordering by multiple columns, it should be a different story.

The biggest problem that I have now is with foreign keys, even if can make some indexes unique with most foreign keys its impossible, 

example of the problem:

CREATE TABLE M_UNICODE (

    S1  VARCHAR(10) NOT NULL COLLATE UNICODE,

    S2  VARCHAR(10) NOT NULL COLLATE UNICODE

);

CREATE TABLE D_UNICODE (

    S1  VARCHAR(10) NOT NULL COLLATE UNICODE,

    S2  VARCHAR(10) NOT NULL COLLATE UNICODE

);

CREATE TABLE M_CI_AI (

    S1  VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI,

    S2  VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI

);

CREATE TABLE D_CI_AI (

    S1  VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI,

    S2  VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI

);

INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'A');

INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'B');

INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'A');

INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'B');

set term ^ ;

execute block

as

  declare variable i bigint = 1000;

begin

 while (i > 0) do

 begin

   insert into d_unicode (s1,s2) values ('A','A');

   insert into d_ci_ai (s1,s2) values ('A','A');

   i = i-1;

 end

 insert into d_unicode (s1,s2) values ('A','B');

 insert into d_ci_ai (s1,s2) values ('A','B');

end^

set term ; ^

ALTER TABLE M_UNICODE ADD CONSTRAINT M_U PRIMARY KEY (S1, S2);

ALTER TABLE M_CI_AI ADD CONSTRAINT M_CI_AI PRIMARY KEY (S1, S2);

commit work;

ALTER TABLE D_UNICODE ADD CONSTRAINT D_U FOREIGN KEY (S1, S2) REFERENCES M_UNICODE (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE D_CI_AI ADD CONSTRAINT D_CI_AI FOREIGN KEY (S1, S2) REFERENCES M_CI_AI (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;

commit work;

Query

------------------------------------------------

 update m_ci_ai set s2 = 'C' where s1 = 'A' and s2 = 'B';

Operations

------------------------------------------------

Read   : 9

Writes : 0

Fetches: 2 070

Marks  : 6

Enchanced Info:

+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |

|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |

+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

|D_CI_AI                        |         0 |      2002 |           0 |       1 |       0 |       0 |        0 |        0 |        0 |

|M_CI_AI                        |         0 |         2 |           0 |       1 |       0 |       0 |        0 |        0 |        0 |

+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Query

------------------------------------------------

 update m_unicode set s2 = 'C' where s1 = 'A' and s2 = 'B';

Operations

------------------------------------------------

Read   : 0

Writes : 0

Fetches: 43

Marks  : 8

Enchanced Info:

+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |

|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |

+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

|D_UNICODE                      |         0 |         2 |           0 |       1 |       0 |       0 |        0 |        0 |        0 |

|M_UNICODE                      |         0 |         2 |           0 |       1 |       0 |       0 |        0 |        0 |        0 |

+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

I made the biggest mistake, I assumed, but I believed that the utf8  colations in firebird where mature.

Thank you

regards

Luis Forra

---