[Fixed in 2018.1.1 + quick fix] Anomaly in German sorting
Since centuries the German language carries along a special character
that had no upper case representation. End o f June 2017 there was the
official decision that lower case ß $c(223) now also has an upper case
representation $c($zhex("1e9e")) looking pretty similar in most fonts.
[ISO defined this character already in 2008.]
Investigating $zconvert(...,"U") and $system.SQL.UPPER(),$system.SQL.SQLUPPER()
it turned out that $c(223) is unchanged during conversion.
No big issue for presentation as both "ß" look alike.
But bad for sorting.
With NLS correctly set to DEUW you get for lower case:
USER>zw low
low("weiser")=1
low("weißer")=1
low("weiter")=1
but for upper case it's broken:
USER>zw up
up("WEISER")=1
up("WEITER")=1
up("WEIßER")=1
Similar for global collation GERMAN3
Technically the sort is correct as lower case sorts after upper case.
The issue is already reported as prodlog 147715
I think you should be aware of this unexpected behavior for all those cases
where you don't have control over the content.
Eg. in text analysis or similar high volume input streams.
In past in typical properties this was often cheated using SS or SZ instead.
But this means changing the content and the length.
Switzerland just eliminated ß as valid character some years ago. Another approach.
Thanks Robert
I just got this information:
Just one file should be changed for it, and it is less than an hour of work.
You can fix this error directly now, if you don't want to wait for the release of version 2018.1.1.
To do this, follow these steps:
(Where to insert)
(That to insert)
<FromToItem FromToKey="55,55,2">7838;</FromToItem>
<FromToItem FromToKey="55,55,3">83,7838;</FromToItem>
<FromToItem FromToKey="83,83">55,55;1</FromToItem>
<FromToItem FromToKey="83,7838">55,55;3</FromToItem>
Just in case, restart Caché.
Now, run a small test
My result:
WOW !



Great stuff. I almost can't believe it.