Different results on 9i and 10g

I have this query that return different results when executing on ORacle 9.2.0.7 and 10g (10.1.0.4, 10.2.0.1 or 10.2.0.2). On 9i - everything is OK, but on 10g - I do not get results if the first OR have values that are not in the table. Like this is an AND not OR.
Also, if I remove the second OR, this query also works fine.
I have this issue permanently when using OCI calls, on 10.2.0.1 I coudl reproduce it also via jdbc connection (tool that connect to Oracle)
Below is the example.
I would be very thankful for any idea on why this is happening.

Thanks a lot for the help, mj

drop table ADDRESS_TEST
CREATE TABLE ADDRESS_TEST ( ADDR_ID NUMBER NOT NULL, ENT_ID NUMBER NOT NULL, ADDR1 VARCHAR2(255), ADDR2 VARCHAR2(255),
ADDR3 VARCHAR2(255), CITY VARCHAR2(255), STATE VARCHAR2(255), COUNTRY VARCHAR2(100),
COUNTRY_CODE VARCHAR2(6), POSTAL_CODE VARCHAR2(20), SYS_DELETE_DT DATE) ;

CREATE INDEX IX_ADDRESS_test_ADDR1 ON ADDRESS_TEST(UPPER(ADDR1)) ;
CREATE INDEX IX_ADDR_test_CT_ST_CTRY ON ADDRESS_TEST(UPPER(CITY), UPPER(STATE), UPPER(COUNTRY)) ;
CREATE INDEX IX_ADDR_ENT ON ADDRESS_test(ENT_ID) REVERSE;

ALTER TABLE ADDRESS_test ADD CONSTRAINT PK_ADDR_test PRIMARY KEY(addr_ID) USING INDEX;

Insert into ADDRESS_TEST values (1, 1,'123 Main street', null, null,'Las Vegas', 'nv', 'USA', 'us', '89119', null);
Insert into ADDRESS_TEST values (2, 1,'123 Main street', null, null,'Las Vegas', 'NV', 'USA', 'us', '89074', null);


This statement doesn't return result set although it should:
SELECT ENT_ID FROM ADDRESS_test WHERE
(UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL )
OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL)
OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL)

This one works because the first OR postal_code has the correct data. Actual if any of the ORs has the correct corresponding data, the whole statement returns the result.
SELECT ENT_ID FROM ADDRESS_test WHERE
(UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL )
OR (UPPER(POSTAL_CODE) = '89119' AND SYS_DELETE_DT IS NULL)
OR (UPPER(POSTAL_CODE) = '89074' AND SYS_DELETE_DT IS NULL)


This one also works because there is only 1 OR:
SELECT ENT_ID FROM ADDRESS_test WHERE
(UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL )
OR (UPPER(POSTAL_CODE) = '89119' AND SYS_DELETE_DT IS NULL);

Tried brackets aroung the 2 ORs but no change there.

You are in troubles my fried, in my environment both versions of Oracle return the expected results...

I'm sorry but the closest versions I could get are 9.2.0.4.0 and 10.2.0.2.0

>>> Ora9i <<<

Code:

---

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> set linesize 180
SQL> set pagesize 0
SQL>
SQL> CREATE TABLE ADDRESS_TEST ( ADDR_ID NUMBER NOT NULL, ENT_ID NUMBER NOT NULL, ADDR1 VARCHAR2(255
), ADDR2 VARCHAR2(255),
  2  ADDR3 VARCHAR2(255), CITY VARCHAR2(255), STATE VARCHAR2(255), COUNTRY VARCHAR2(100),
  3  COUNTRY_CODE VARCHAR2(6), POSTAL_CODE VARCHAR2(20), SYS_DELETE_DT DATE) ;

Table created.

SQL> CREATE INDEX IX_ADDRESS_test_ADDR1 ON ADDRESS_TEST(UPPER(ADDR1)) ;

Index created.

SQL> CREATE INDEX IX_ADDR_test_CT_ST_CTRY ON ADDRESS_TEST(UPPER(CITY), UPPER(STATE), UPPER(COUNTRY))
 ;

Index created.

SQL> CREATE INDEX IX_ADDR_ENT ON ADDRESS_test(ENT_ID) REVERSE;

Index created.

SQL> ALTER TABLE ADDRESS_test ADD CONSTRAINT PK_ADDR_test PRIMARY KEY(addr_ID) USING INDEX;

Table altered.

SQL> Insert into ADDRESS_TEST values (1, 1,'123 Main street', null, null,'Las Vegas', 'nv', 'USA', '
us', '89119', null);

1 row created.

SQL> Insert into ADDRESS_TEST values (2, 1,'123 Main street', null, null,'Las Vegas', 'NV', 'USA', '
us', '89074', null);

1 row created.

SQL> SELECT  ENT_ID
  2  FROM    ADDRESS_test
  3  WHERE  (UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL
 )
  4      OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL)
  5      OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL)
  6  ;
        1
        1

SQL> SELECT  ENT_ID
  2  FROM    ADDRESS_test
  3  WHERE  (UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL
 )
  4      OR (UPPER(POSTAL_CODE) = '89119' AND SYS_DELETE_DT IS NULL)
  5      OR (UPPER(POSTAL_CODE) = '89074' AND SYS_DELETE_DT IS NULL)
  6  ;
        1
        1

SQL> SELECT  ENT_ID
  2  FROM    ADDRESS_test
  3  WHERE  (UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL
 )
  4      OR (UPPER(POSTAL_CODE) = '89119' AND SYS_DELETE_DT IS NULL);
        1
        1

SQL>

---

>>> Ora10g <<<

Code:

---

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE ADDRESS_TEST ( ADDR_ID NUMBER NOT NULL, ENT_ID NUMBER NOT NULL, ADDR1 VARCHAR2(255
), ADDR2 VARCHAR2(255),
  2  ADDR3 VARCHAR2(255), CITY VARCHAR2(255), STATE VARCHAR2(255), COUNTRY VARCHAR2(100),
  3  COUNTRY_CODE VARCHAR2(6), POSTAL_CODE VARCHAR2(20), SYS_DELETE_DT DATE) ;

Table created.

SQL> CREATE INDEX IX_ADDRESS_test_ADDR1 ON ADDRESS_TEST(UPPER(ADDR1)) ;

Index created.

SQL> CREATE INDEX IX_ADDR_test_CT_ST_CTRY ON ADDRESS_TEST(UPPER(CITY), UPPER(STATE), UPPER(COUNTRY))
 ;

Index created.

SQL> CREATE INDEX IX_ADDR_ENT ON ADDRESS_test(ENT_ID) REVERSE;

Index created.

SQL> ALTER TABLE ADDRESS_test ADD CONSTRAINT PK_ADDR_test PRIMARY KEY(addr_ID) USING INDEX;

Table altered.

SQL> Insert into ADDRESS_TEST values (1, 1,'123 Main street', null, null,'Las Vegas', 'nv', 'USA', '
us', '89119', null);

1 row created.

SQL> Insert into ADDRESS_TEST values (2, 1,'123 Main street', null, null,'Las Vegas', 'NV', 'USA', '
us', '89074', null);

1 row created.

SQL> SELECT  ENT_ID
  2  FROM    ADDRESS_test
  3  WHERE  (UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL
 )
  4      OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL)
  5      OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL)
  6  ;

    ENT_ID
----------
        1
        1

SQL> SELECT  ENT_ID
  2  FROM    ADDRESS_test
  3  WHERE  (UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL
 )
  4      OR (UPPER(POSTAL_CODE) = '89119' AND SYS_DELETE_DT IS NULL)
  5      OR (UPPER(POSTAL_CODE) = '89074' AND SYS_DELETE_DT IS NULL)
  6  ;

    ENT_ID
----------
        1
        1

SQL> SELECT  ENT_ID
  2  FROM    ADDRESS_test
  3  WHERE  (UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL
 )
  4      OR (UPPER(POSTAL_CODE) = '89119' AND SYS_DELETE_DT IS NULL);

    ENT_ID
----------
        1
        1

---

I would open a TAR with Oracle including full listing of your case study showing the issue along side with event 10046/10053 traces.

Thanks a lot for the help.
So, fisrt if you know - how can I duplicate the calls that are made via OCI?
When I run the code via SQL plus, I get the results you get. But when the same query was run via application that uses OCI, then I get nothing.
Only on 10.2.0.1 I was able to reproduce it via some tools like DBVisualizer and ORaEdit, and sqlPlus.
Secondly, here is the explain plan form 10.2.0.1 machine:
SQL> SELECT ENT_ID FROM ADDRESS_test WHERE
2 (UPPER(CITY) LIKE 'LAS%' AND UPPER(STATE) = 'NV' AND SYS_DELETE_DT IS NULL) OR
3 (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL) OR
4 (UPPER(POSTAL_CODE) = '88888' AND SYS_DELETE_DT IS NULL) ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3645838471

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 994K| 21M| 5922 (3)| 00:01:12 |
|* 1 | TABLE ACCESS FULL| ADDRESS_test | 994K| 21M| 5922 (3)| 00:01:12 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("SYS_DELETE_DT" IS NULL AND (UPPER("POSTAL_CODE")='99999' OR
UPPER("POSTAL_CODE")='88888'))
I do not see here the city predicate at all? How this is posible?
Thanks a lot, mj

Ok, here is one more update on this problem: If I remove all UPPER() from the query and have the proper case values, I get the correct result form both SQL Plus and OCI calls.
Is there a know issue with UPPER function for indexes in versions 10.1 or 10.2?
Thanks a lot, mj

Since you get good results when working via SQLPLUS that means all underlying backend Oracle objects including tables and indexes are just fine.

Problem is more likely on the client side; somehow your OCI calls are not asking Oracle what you want to ask.

My wild guess is you have a client component corrupt or on the wrong version; something subtle, not enough to blow up in your hands but enough to cause you a headache.

Here is an OCI tracing tool... http://www.geocities.com/ocispy/

Thanks a lot. I have already changed 2 clients - ora 9.2.0.7 and 10.2.0.2 as they are the only acceptable for the application. I get the same results. How can I check for possiblem client problems?
Thanks again, mj

I think is time to open a Ticket with Oracle.
You have a solid case.

Thanks a lot for the help, guys!
The issue was resolved yesterday after hours of testing.

All I had to do is to add a TRIM() to the Upper function. For example:
Via OCI (and in some cases SQLPlus), the first query does not return results in Ora 10g, but the second will always return the records expected.
Query with no results:
SQL> SELECT ENT_ID FROM ADDRESS_test WHERE
(UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL )
OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL)
OR (UPPER(POSTAL_CODE) = '99999' AND SYS_DELETE_DT IS NULL);

Query with good result set returned:
SQL> SELECT ENT_ID FROM ADDRESS_test WHERE
(UPPER(CITY) LIKE upper('Las%') AND UPPER(STATE) = upper('nv') AND SYS_DELETE_DT IS NULL )
OR (UPPER(trim(POSTAL_CODE)) = '99999' AND SYS_DELETE_DT IS NULL)
OR (UPPER(trim(POSTAL_CODE)) = '99999' AND SYS_DELETE_DT IS NULL);

The problem is that these fileds are all VARCHAR2(100)/(20)/(50) and it looks that ORacle is treating them as CHARS... When I select the values from these columns, there are no characters appended to the actual value...
So, my question now is: how Oracle 10g applies the UPPER function compare to Ora 9i? Obviously 9.2.0.7 doesn't have this problem. Is this bug in 10g (all versions)?
Thanks a lot for the help,
mj

I would like to share my problem today I have faced.

I have two databases : one is 10.1.0.2.0 and 2nd is 9.2.07.

I have run query from SQL plus to sum of AMT column then from both database i got same result.

But when i run report (Reports 6i) from both databases, i got different result set. No error found during error.

After that I have tried to rebuild indexes.

On 9.2.0.7 has given me error regarding duplicate value while rebulding primary key index.

So I found the duplicate value from my table and tried to delete then I got following error.

ORA-08102: index key not found, obj# 32394, dba 54633325 (2).

So i have create the another table from old one, drop old one and recreate.

So due to corruption I got different result set on database.