NLS_COMP & NLS_SORT issues in Oracle 10g Release 2

From: <stvchien0_at_gmail.com>
Date: Thu, 05 Jul 2007 17:01:18 +0800
Message-ID: <1qcp83dhsf8omg2dtn6k68em52707oa1f5@4ax.com>

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

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

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

Received on Thu Jul 05 2007 - 04:01:18 CDT