Why does Oracle's varchar sort order not match the behavior of varchar comparison?

Question

An SQL statement like:

select * from (
  select '000000000000' as x from dual
  union
  select '978123456789' as x from dual
  union 
  select 'B002AACD0A' as x from dual
) /*where x>'000000000000'*/ order by x;

Yields:

B002AACD0A
000000000000
978123456789

After uncommenting the WHERE-restriction, the result is:

B002AACD0A
978123456789

I would have expected the result to be just 978123456789 since B002AACD0A is returned before 000000000000 when running the query without restriction.

How can this behavior be explained? And how am I supposed to sort and compare varchars so that they can work together like I can do with integers?

Funny enough, when changing the restriction to x>'B002AACD0A', the result is empty. Changing it tox>978123456789 returns B002AACD0A.

I.e. when comparing:

B002AACD0A > 978123456789 > 000000000000

But when sorting:

978123456789 > 000000000000 > B002AACD0A 

When using binary sort explicitely (order by NLSSORT(x,'NLS_SORT=BINARY_AI')), the result is B002AACD0A>978123456789>000000000000 and matches the behavior of comparison. But I still do not know why this is happening.

Answer 1

Peter,

the behaviour of the sorting is regulated by the NLS_SORT session parameter, whereas the behaviour for comparisons is dependent upon the NLS_COMP parameter. You must have a mismatch.

I obtain the same result as you do with the following parameters:

SQL> SELECT *
  2    FROM nls_session_parameters
  3   WHERE parameter IN ('NLS_COMP', 'NLS_SORT');

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       FRENCH
NLS_COMP                       BINARY

However when the two are matched the result is consistent:

SQL> alter session set nls_comp=LINGUISTIC;

Session altered

SQL> select * from (
  2    select '000000000000' as x from dual
  3    union
  4    select '978123456789' as x from dual
  5    union
  6    select 'B002AACD0A' as x from dual
  7  ) /*where x>'000000000000'*/ order by x;

X
------------
B002AACD0A
000000000000
978123456789

SQL> select * from (
  2    select '000000000000' as x from dual
  3    union
  4    select '978123456789' as x from dual
  5    union
  6    select 'B002AACD0A' as x from dual
  7  ) where x > '000000000000' order by x;

X
------------
978123456789