Brett Wooldridge created DERBY-6607:
---------------------------------------
Summary: Derby is using territory/collation for equality, not just
ordering (incorrectly?)
Key: DERBY-6607
URL: https://issues.apache.org/jira/browse/DERBY-6607
Project: Derby
Issue Type: Bug
Components: Localization
Affects Versions: 10.10.2.0
Reporter: Brett Wooldridge
We have a database where we wish case-insensitivity, and therefore it was
created with collation=TERRITORY_BASED:PRIMARY. We have customers in both the
United States (en_US) and in Japan (ja_JP).
We have an issue in Japan. Japanese has three character sets: hiragana,
katakana, and kanji. Hiragana is a phonetic alphabet with 46 letters.
Katakana is an identical phonetic alphabet with 46 letters, written using
different character forms, and used for foreign words (words adopted from other
languages into Japanese).
Here is the word 'cake' written in katakana: ケーキ (ke- ki)
Here is the word 'cake' written in hiragana: けーき (ke- ki)
In terms of collation (ordering), Japanese consider these to be equal. So, in
the following Java code, the call to 'compare()' would return 0:
{code:java}
Collator collator = Collator.getInstance(Locale.JAPAN);
collator.setStrength(Collator.PRIMARY);
return collator.compare("ケーキ", "けーき");
{code}
And therein lies the issue. With respect to _ordering_ they are indeed
equivalent, however Japanese would consider them district (non-equivalent)
values.
When a table is declared with a UNIQUE constraint on a column, or a PRIMARY KEY
column, if 'ケーキ' exists in the table, Derby will throw a unique constraint
violation upon an attempt to insert 'けーき'.
We need collation=TERRITORY_BASED:PRIMARY or TERRITORY_BASED:SECONDARY for
case-insensitivity _and_ at the same time need these values to be treated as
unique.
Is it "correct" that Derby use the collation when determining value equivalency
vs. ordering equivalency? It seems to us that collation should only be used
with respect to ordering, but not with respect to DML (INSERT, UPDATE, DELETE).
--
This message was sent by Atlassian JIRA
(v6.2#6252)
