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