Hi there,

I get strange results by using collate UNICODE_CI_AI and UNICODE_CI on Firebird 2.5 and 3.0.
These seem like working just as same as collate UNICODE.

Test case 1. Firebird 2.5 embedded (Windows 7)

CREATE TABLE A (F1 INTEGER NOT NULL PRIMARY KEY, F2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL);
INSERT INTO A (F1,F2)  VALUES (1,'ç');
INSERT INTO A (F1,F2)  VALUES (2,'C');
INSERT INTO A (F1,F2)  VALUES (3,'c');
SELECT F2,F1 FROM A ORDER BY F2 COLLATE UNICODE_CI_AI, F1;

F2    F1
---    ---
c    3
C    2
ç    1

The result that I expected was 1,2,3 in F1 value sequence. But resulted 3,2,1 as shown above just like COLLATE UNICODE.
Expected 2,3,1 for UNICODE_CI as well but all of these three collations resulted the same.

Test case 2. Firebird 3.0; iSQL (Windows 7)

SQL> CREATE DATABASE 'TEST.FDB';
SQL> CREATE TABLE A (F1 INTEGER NOT NULL PRIMARY KEY, F2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL);
SQL> INSERT INTO A (F1,F2)  VALUES (1,'c');
SQL> INSERT INTO A (F1,F2)  VALUES (2,'C');
SQL> INSERT INTO A (F1,F2)  VALUES (3,'c');
SQL> INSERT INTO A (F1,F2)  VALUES (4,'C');
SQL> SELECT F2,F1 FROM A ORDER BY F2 COLLATE UNICODE_CI, F1;

F2                   F1
========== ============
c                     1
c                     3
C                     2
C                     4

Any suggestions?

Thanks in advance

Hiro