英文:
Firebird unicode SQL search behaves different on LIKE and = on some characters
问题
在 Gentoo Linux 上运行 Firebird Classic v2.5.9,并使用系统范围的 ICU 库(版本 72)时,我发现在使用 = 运算符和 LIKE 运算符进行搜索时,对于某些 Unicode 字符(在我的情况下是波兰字母“ł”),结果出现了奇怪的差异。
我将用例简化为以下示例:
select
case
when 'l' collate unicode_ci_ai = 'ł' collate unicode_ci_ai
then 'equal'
else 'differ'
end
from
rdb$database;
-- 结果: equal
select
case
when 'l' collate unicode_ci_ai like 'ł' collate unicode_ci_ai
then 'equal'
else 'differ'
end
from
rdb$database;
-- 结果: differ
然
英文:
Running Firebird Classic v2.5.9 on Gentoo Linux and using system wide ICU library (version 72) I came across strange search results between the = operator and the LIKE operator search regarding certain unicode characters (in my case the polish l).
I simplified the use case to the following example:
select
case
when 'l' collate unicode_ci_ai = 'ł' collate unicode_ci_ai
then 'equal'
else 'differ'
end
from
rdb$database;
-- Result: equal
select
case
when 'l' collate unicode_ci_ai like 'ł' collate unicode_ci_ai
then 'equal'
else 'differ'
end
from
rdb$database;
-- Result: differ
Paradoxically: when I create a unique index on a column with a unicode_ci_ai
collation and a unique constraint things behave different:
CREATE TABLE foo
(
COL1 VARCHAR(10) COLLATE UNICODE_CI_AI
);
ALTER TABLE foo ADD CONSTRAINT UNQ1_FOO UNIQUE (COL1);
INSERT INTO foo VALUES ('l');
-- Result: OK
INSERT INTO foo VALUES ('ł');
-- Result: Error
-- violation of PRIMARY or UNIQUE KEY constraint "UNQ1_FOO" on table "FOO".
-- Problematic key value is ("COL1" = 'l').
With other 'non-standard' characters (é for example) the both search strategies give the same result:
select case when 'e' collate unicode_ci_ai = 'é' collate unicode_ci_ai then 'equal' else 'differ' end from rdb$database;
-- Result: equal
select case when 'e' collate unicode_ci_ai like 'é' collate unicode_ci_ai then 'equal' else 'differ' end from rdb$database;
-- Result: equal
Is it a bug or a feature? It the latter - then why?
答案1
得分: 1
问题似乎已由Firebird团队解决:
https://github.com/FirebirdSQL/firebird/issues/5044
并且已经在4.0 Beta 2中修复。
英文:
The issue seems to be tackled by the Firebird team:
https://github.com/FirebirdSQL/firebird/issues/5044
and already fixed in 4.0 Beta 2.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论