Oracle普通ORDER BY与LISTAGG内ORDER BY排序结果不一致问题

结论 #

这不属于Oracle BUG，是两类ORDER BY的默认排序规则生效逻辑不同导致的。

前置条件 #

运行环境：Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
会话NLS配置：NLS_SORT=HUNGARIAN、NLS_COMP=BINARY

差异原因 #

• 普通查询的顶层ORDER BY默认遵循会话级NLS语言排序规则：HUNGARIAN排序规则下，重音字符Á属于A类字符，排序优先级在A之后、B之前，因此返回结果符合预期：

A
Á
B
C

• LISTAGG聚合函数内部的ORDER BY默认采用二进制排序规则：当NLS_COMP=BINARY时，聚合函数内部的排序操作会直接按字符的二进制编码值比较大小，Á的二进制编码值大于C的编码值，因此最终拼接结果为A, B, C, Á。

对齐两种排序逻辑的方法 #

在LISTAGG的ORDER BY子句中显式指定按匈牙利语规则排序，修改SQL如下：

select listagg(letter,', ') within group (order by NLSSORT(letter, 'NLS_SORT=HUNGARIAN')) 
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一致的拼接结果。

内容的提问来源于stack exchange，提问作者Feri