Oracle will ignore positions 1000 and after in a string field or expression for sorting using an ORDER BY clause.

From: Roel Schreurs <schreurs_roel_at_hotmail.com>
Date: 13 Jan 2005 04:42:01 -0800
Message-ID: <321ebdef.0501130442.3713b905@posting.google.com>

Hi all,

I could not find any postings on the following problem, so here it is.

Oracle will ignore positions 1000 and after in a string field or expression for sorting using an ORDER BY clause.

If strings only differ after position 999, they will be treated as equal and sorting will be unpredictable, if the field is the last in the order by clause, or subsequent fields will be used.

This occurs in both Oracle 8 and 9.

Below steps to reproduce the problem.

create table test_order_by(num number(10) not null, val varchar2(2000) not null);
-- These strings differ on position 999 and will be sorted correctly.

insert into test_order_by values(1, lpad('X', 998, 'X') || 'A' );
insert into test_order_by values(2, lpad('X', 998, 'X') || 'B' );
insert into test_order_by values(3, lpad('X', 998, 'X') || 'C' );

-- These strings differ on position 1000 and will be sorted
incorrectly.

insert into test_order_by values(4, lpad('X', 999, 'X') || 'D' );
insert into test_order_by values(5, lpad('X', 999, 'X') || 'E' );
insert into test_order_by values(6, lpad('X', 999, 'X') || 'F' );

• This query should effectively sort NUM ascending. select num from test_order_by order by val , num desc -- force sorting on a subsequent field, to avoid unpredictable soting, which might not demonstrate the problem.

The result:

        NUM

---

          1
          2
          3
          6
          5
          4

Note that the values of rows 4,5 and 6 are treated as equal.

If the ORDER BY clause contains an expression, the problem also occurs.

select num
from test_order_by
order by lpad('X', case when num in (1,2,3) then 998 else 999 end, 'X') || CHR(ASCII('A') + num - 1)

       , num desc

(In case you were wondering why I would like to sort string that differ so little, I construct a long string of descriptions of all ancestors in an hierarchic query to sort siblings on that description.)

Roel Schreurs Received on Thu Jan 13 2005 - 06:42:01 CST