We face a very special behavior in one of our migration projects where we want to move from Oracle 11.1 to 11.2.
The old query was in the following format:
SELECT * FROM TABLE_A, TABLE_X WHERE
(NVL(TO_NUMBER(COL_A), 0) > 0 OR NVL(TO_NUMBER(COL_B), 0) > 0)
AND
(INSTR('Dummy', COL_X) > 0 OR COL_X_ID = 100)
AND COL_A_ID = COL_X_ID
In Oracle 11.1 everything works smoothly even though column COL_B contains alphanumeric values!
Oracle 11.2
In comparison the new version reports an "invalid number" error.
When we switch the items of the logical operator AND it also works on this version:
SELECT * FROM TABLE_A, TABLE_X WHERE
(INSTR('Dummy', COL_X) > 0 OR COL_X_ID = 100)
AND
(NVL(TO_NUMBER(COL_A), 0) > 0 OR NVL(TO_NUMBER(COL_B), 0) > 0)
AND COL_A_ID = COL_X_ID
Unfortunately, we have many hundreds of queries and a shortage of resources refactoring the queries.
Question
Is there a flag determine the behavior of the AND operator?