Inconsistent sorting results

Question

I am trying to use collate binary_ci in order by and I got inconsistent results from two queries. I expect those two results are supposed to be the same.

first query:

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','B','b','ä'))
order by name collate binary_ci;

result:

name	sort_value
A	6100
a	6100
B	6200
b	6200
ä	C3A400

second query:

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','ä','B','b'))
order by name collate binary_ci;

result:

name	sort_value
A	6100
a	6100
b	6200
B	6200
ä	C3A400

The only difference is where ä is. I expect the same result as first query no matter how these 5 letters ordered.

And my NLS settings:

PARAMETER	VALUE
NLS_LANGUAGE	ENGLISH
NLS_TERRITORY	DENMARK
NLS_CURRENCY	kr
NLS_ISO_CURRENCY	DENMARK
NLS_NUMERIC_CHARACTERS
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	RR
NLS_DATE_LANGUAGE	ENGLISH
NLS_SORT	BINARY
NLS_TIME_FORMAT	HH24
NLS_TIMESTAMP_FORMAT	RR
NLS_TIME_TZ_FORMAT	HH24
NLS_TIMESTAMP_TZ_FORMAT	RR
NLS_DUAL_CURRENCY	€
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

Oracle version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.18.0.0.0

Any idea of why it behave like this and how can I make the result consistent? Thanks in advance.

Answer 1

The sort is non-deterministic.

With the binary_ci, 'a' and 'A' have the same sort code, so the database is free to return these in either order. Same applies to 'b' and 'B'.

If you want to guarantee the two queries return the rows in the same order, you need to order by unique values.

Assuming you case-insensitive sorting first, you can do this by adding the (uncollated) name to the end of the order by

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','B','b','ä'))
order by name collate binary_ci, name;

NAME    SORT_VALUE    
A       6100          
a       6100          
B       6200          
b       6200          
ä       C3A400  

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','ä','B','b'))
order by name collate binary_ci, name;

NAME    SORT_VALUE    
A       6100          
a       6100          
B       6200          
b       6200          
ä       C3A400