Firebird sort order with other character set

Question

The sorting in this query does not take in account signs, only letters:

SELECT CAST(Text AS VARCHAR(20) CHARACTER SET ISO8859_1) COLLATE NO_NO Result FROM (
select CAST('_Anon' AS VARCHAR(20)) COLLATE UNICODE_CI_AI as Text from RDB$DATABASE
UNION
SELECT CAST('Abba' AS VARCHAR(20)) COLLATE UNICODE_CI_AI AS Text from  RDB$DATABASE
UNION
SELECT CAST('Beatles' AS VARCHAR(20)) COLLATE UNICODE_CI_AI AS Text from  RDB$DATABASE)
ORDER BY Result

Expected sort order(non-alpha-numeric before any letter):

_Anon
Abba
Beatles

But I get:

Abba
_Anon
Beatles

The collation does not matter. If you delete "COLLATE NO_NO" it still sorts wrong.

Edit: Found that collation ES_ES sorts this correct, but it fails to sort Norwegian characters.

Is this a bug or am I missing something in this query?

What I'm trying to do is to get correct sort order in Norwegian, and none of the collations in UNICODE_CI_AI gives me the correct order.

Update: Expanded the example with another sub-query so that it clearer shows the point.

Answer 1

Marks hint to look at the collation pointed me in the direction of a solution.

I do consider this a bug, so I was going to file a a bug report to firebirdsql, but found out it's a "Won't fix" and the workaround below is the official fix.

Of all base collations defined ES_* is the only one with the attribute: SPECIALS-FIRST=1 set. In fact it's the only collation with any attribute set.

And that attribute defines that special characters should be sorted before alphanumeric characters.

So the workaround is to create a new collation based on the NO_NO collation:

CREATE COLLATION NO_NO_NOPAD_CI_SF
FOR ISO8859_1
FROM NO_NO
NO PAD
CASE INSENSITIVE
'SPECIALS-FIRST=1';

then using the new collation like this:

SELECT CAST(Text AS VARCHAR(20) CHARACTER SET ISO8859_1) COLLATE NO_NO_NOPAD_CI_NUM_SF Result FROM (
select CAST('_Anon' AS VARCHAR(20)) COLLATE UNICODE_CI_AI as Text from RDB$DATABASE
UNION
SELECT CAST('Abba' AS VARCHAR(20)) COLLATE UNICODE_CI_AI AS Text from  RDB$DATABASE
UNION
SELECT CAST('Beatels' AS VARCHAR(20)) COLLATE UNICODE_CI_AI AS Text from  RDB$DATABASE)
ORDER BY Result 

Yields the expected result:

_Anon
Abba
Beatles