Re: NLS_COMP & NLS_SORT issues in Oracle 10g Release 2

On Thu, 05 Jul 2007 17:01:18 +0800, stvchien0_at_gmail.com wrote:

>Hi,
>
> Lately, we encounterd some weird issues with Oracle 10g Release 2
>running on Windows 2003. It seems to have something to do with
>NLS_COMP & NLS_SORT settings. Appreciate if anyone can help!
>
> Let me describe how you can reproduce the issues.
>
> Before we get into the main topic, I would like to pass along the
>NLS-related settings on the database-level first.
>
>SQL> select * from nls_database_parameters;
>
>PARAMETER VALUE
>------------------------------
>--------------------------------------------------------------------------------
>NLS_LANGUAGE AMERICAN
>NLS_TERRITORY AMERICA
>NLS_CURRENCY $
>NLS_ISO_CURRENCY AMERICA
>NLS_NUMERIC_CHARACTERS .,
>NLS_CHARACTERSET AL32UTF8
>NLS_CALENDAR GREGORIAN
>NLS_DATE_FORMAT DD-MON-RR
>NLS_DATE_LANGUAGE AMERICAN
>NLS_SORT BINARY
>NLS_TIME_FORMAT HH.MI.SSXFF AM
>NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
>NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
>NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
>NLS_DUAL_CURRENCY $
>NLS_COMP BINARY
>NLS_LENGTH_SEMANTICS BYTE
>NLS_NCHAR_CONV_EXCP FALSE
>NLS_NCHAR_CHARACTERSET AL16UTF16
>NLS_RDBMS_VERSION 10.2.0.1.0
>
> Then, let's create the test table and insert some data.
>
>-- creates the table
>create table TEST_FOR_BUG
>(
> STATUS VARCHAR2(11),
> REQUIREDDATE TIMESTAMP(6)
>);
>
>-- creates indexes
>create index I_TEST_FOR_BUG on TEST_FOR_BUG
>(NLSSORT(STATUS,'nls_sort=BINARY_CI'));
>
>-- inserts the test data
>insert into TEST_FOR_BUG (STATUS, REQUIREDDATE)
>values ('Pending', to_timestamp('14-03-2007 00:00:00.000000',
>'dd-mm-yyyy hh24:mi:ss.ff'));
>
>insert into TEST_FOR_BUG (STATUS, REQUIREDDATE)
>values ('Pending', to_timestamp('15-08-2007 00:00:00.000000',
>'dd-mm-yyyy hh24:mi:ss.ff'));
>
>insert into TEST_FOR_BUG (STATUS, REQUIREDDATE)
>values ('Pass', to_timestamp('02-07-2007 00:00:00.000000', 'dd-mm-yyyy
>hh24:mi:ss.ff'));
>
>insert into TEST_FOR_BUG (STATUS, REQUIREDDATE)
>values ('Pending', to_timestamp('02-07-2007 00:00:00.000000',
>'dd-mm-yyyy hh24:mi:ss.ff'));
>
>commit;
>
>
>Condition I.
>
>alter session set nls_comp=linguistic;
>alter session set nls_sort=binary_ci;
>alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';
>alter session set nls_date_format='YYYY-MM-DD';
>
>select *
> from test_for_bug t
> WHERE ((T.status <> 'Deleted') AND (T.status <> 'Pass') AND
> (T.requireddate >= '2007-07-04 00:00:00'))
> OR ((T.status = 'Pass'))
> OR ((T.status <> 'Pass') AND (T.status <> 'Deleted') AND
> (T.requireddate < '2007-07-04 00:00:00'))
> OR ((T.status = 'Deleted'));
>
> We got,
>
>SQL>
>
>Session altered
>
>Session altered
>
>Session altered
>
>Session altered
>
>STATUS REQUIREDDATE
>-----------
>--------------------------------------------------------------------------------
>Pass 2007-07-02 00:00:00.000000
>
>Condition II.
>
>alter session set nls_comp=binary;
>alter session set nls_sort=binary;
>alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';
>alter session set nls_date_format='YYYY-MM-DD';
>
>select *
> from test_for_bug t
> WHERE ((T.status <> 'Deleted') AND (T.status <> 'Pass') AND
> (T.requireddate >= '2007-07-04 00:00:00'))
> OR ((T.status = 'Pass'))
> OR ((T.status <> 'Pass') AND (T.status <> 'Deleted') AND
> (T.requireddate < '2007-07-04 00:00:00'))
> OR ((T.status = 'Deleted'));
>
> We got,
>
>SQL>
>
>Session altered
>
>Session altered
>
>Session altered
>
>Session altered
>
>STATUS REQUIREDDATE
>-----------
>--------------------------------------------------------------------------------
>Pending 2007-03-14 00:00:00.000000
>Pending 2007-08-15 00:00:00.000000
>Pass 2007-07-02 00:00:00.000000
>Pending 2007-07-02 00:00:00.000000
>
> We wondered why we got two different results...
>
>- Steve

Hi,

  It should be a bug of Oracle. The problem got resolved after we migrated to the 10.2.0.3.

• Steve