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.