Hi Thomas,
As you suggested and may have already noticed from the other replies,
the problems depends on the NLS_SORT setting.
select SYS_CONTEXT('userenv', 'NLS_SORT') from dual tells me it is DUTCH
on our servers.
Select banner from v$version tells me
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
and
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
alter session set nls_sort=DUTCH;
will help you reproduce the problem.
Best regards,
Roel
Thomas Kyte wrote:
> 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.
>
>
Received on Tue Jan 18 2005 - 12:54:28 CST
