Latin small letter I without dot is treated incorrectly for UNICODE_CI

[ertankucukoglu]

Latin small letter I without dot (U+0131) upper case is latin capital letter I without dot in Turkish. This is probably same in Azerbaijani, Crimean Tatar, Gagauz, Kazakh, Tatar languages. https://en.wikipedia.org/wiki/Dotless_I

There is also this document https://unicode.org/charts/PDF/U0000.pdf
Which is Latin ASCII table document. It has below part in it

I do not know if that small case -> upper case conversion break another language. I actually do not know where to check these conversions as an authority. But above two locations indicate that such conversion is the right one.

If you run below script on v4.0.2, it will successfully insert all statements where I expect it to fail on the last one as it is already inserted as small caps dotless I in the secod insert statement.

/* try this on UTF8 database */
create table test(aname varchar(300) not null primary key collate unicode_ci);
insert into test values('Ali');
insert into test values('Akıl'); /* DOTLESS I SMALL CAPS */
insert into test values('тест');
insert into test values('prüfen');
insert into test values('AKIL'); /* DOTLESS I CAPITAL */

If that is fine, I would appreciate a change in UNICODE_CI to follow this conversion and fail on the last statement in example script.

[mrotteveel]

UNICODE_CI is a generic collation, and as far as I'm aware, it should not apply locale-specific case-insensitivity rules like this one.

As an aside, you created this as a discussion, and not as an issue. If you really want to discuss/ask a question, it might be better to ask on firebird-support or firebird-devel instead of here.

[ertankucukoglu]

Actually, I followed an earlier discussion like in here
Discussion later converted into an issue
It was also about a specific character in German language.

[mrotteveel]

That isn't a discussion though, that is an issue. There is a difference. Issues are basically bug reports or feature requests.

[asfernandes]

create collation unicode_ci_tr_tr for utf8 from unicode_ci 'LOCALE=tr_TR';

recreate table test(aname varchar(300) not null primary key collate unicode_ci_tr_tr);

insert into test values('Ali');
insert into test values('Akıl'); /* DOTLESS I SMALL CAPS */
insert into test values('тест');
insert into test values('prüfen');
insert into test values('AKIL'); /* DOTLESS I CAPITAL */

Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "INTEG_4" on table "TEST"
-Problematic key value is ("ANAME" = 'AKIL')