oracle sorts differently on simple order by and listagg's order by-CodePudding

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

select letter from (
select 'A' as letter from dual union all
select 'Á' as letter from dual union all
select 'B' as letter from dual union all
select 'C' as letter from dual ) t
order by letter;

result (is okay):

A
Á
B
C

But with this

select listagg(letter,', ') within group (order by letter) from (
select 'A' as letter from dual union all
select 'Á' as letter from dual union all
select 'B' as letter from dual union all
select 'C' as letter from dual ) t;

the order of result letters are different:

A, B, C, Á

Is it a simple Oracle bug in the latter case?

(I don't say anything about NLS. I think these queries should work the same way independetly of NLS.)

Update:

Clarification: I'm running these queries one-by-another in SqlDeveloper in the same connection.

NLS_SORT    HUNGARIAN
NLS_COMP    BINARY

(If @MT0's answer is the solution then IMHO it is a bug in oracle using different default NLS setting on a simple order by clause and on a listagg call.)

CodePudding user response：

You need to provide the NLS_SORT setting:

select listagg(letter,', ')
       within group (order by NLSSORT(letter, 'NLS_SORT=BINARY_AI')) AS letters
from (
  select 'A' as letter from dual union all
  select 'Á' as letter from dual union all
  select 'B' as letter from dual union all
  select 'C' as letter from dual
) t;

Outputs:

LETTERS

A, Á, B, C

LETTERS
A, Á, B, C

Note: LISTAGG does not appear to use the session NLS_SORT setting in its ORDER BY clause; but you can pass it in directly as shown above.

If you want to use the session parameter (rather than a specific value):

select listagg(letter,', ')
       within group (
         order by NLSSORT(
                    letter,
                    ( SELECT 'NLS_SORT='||value
                      FROM   NLS_SESSION_PARAMETERS
                      WHERE  parameter = 'NLS_SORT' )
                  )
       ) AS letters
from (
  select 'A' as letter from dual union all
  select 'Á' as letter from dual union all
  select 'B' as letter from dual union all
  select 'C' as letter from dual
) t;

db<>fiddle here

CodePudding user response：

Ordering of letters depends critically on NLS_SORT, so what you say at the end of your message is perfectly wrong.

The main question is - are you getting those contradictory results on exactly the same system, with all the same NLS settings? If so, then that's a bug in the implementation of the ORDER BY clause in LISTAGG. It would be good to have a test case - show us your NLS settings (the result of select * from v$nls_parameters) followed immediately by the two queries and their outputs. For good measure, show also select * from v$version (telling us your database version).