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

In article <321ebdef.0501130442.3713b905_at_posting.google.com>, Roel Schreurs says...
>
>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

more details please -- i cannot reproduce in 8174, 9205, 10.1.0.3

ops$tkyte_at_ORA817DEV> select num
  2 from test_order_by
  3 order by val
  4 , num desc;  

       NUM

         1
         2
         3
         4
         5
         6
 

6 rows selected.

nls settings coming into play here? i took it out to 2000 characters as well.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation