Firebird Unicode SQL搜索在某些字符上的LIKE和=操作行为不同。

huangapple go评论62阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年5月17日 18:08:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/76270927.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定