0

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?

Markus
  • 763
  • 7
  • 24
  • SQL Fiddle was not reachable and I could not create an example - Sry :( – Markus Oct 27 '16 at 13:48
  • 3
    Swapping the order of the predicates wouldn't normally affect how the query is executed; but you might have a plan for your first version that was created when the optimiser thought the `col_x` filter was more selective, and now (when it hard-parsed the second query) it doesn't. Did you first run the query with different (or no) data, perhaps? If you gather schema stats do you see any difference; or after a DB bounce/shared pool flush (neither should be done lightly though!)? What dates are shown in `v$sql` for both versions (e.g. first_load_time)? – Alex Poole Oct 27 '16 at 13:53
  • It does not make sense. can you try create table as result. It should give the same error. I think you control your nls params. I mean maybe in your data has ',' on numberic and it gives error on new patch because it wait '.' – CompEng Oct 27 '16 at 13:56
  • 1
    Apparently you relied on some implicit order in which the conditions are evaluated. I would have never expected that query to work in the first place if there are values in `col_a` that are not numbers. I'm actually surprised that changing the order for 11.2 fixes this. And I'm pretty sure that you have no guarantee that this will keep on working. This is another good example why storing numbers in a VARCHAR column is a bad idea –  Oct 27 '16 at 13:56
  • The behavior also changes when I start to add Oracle hints (ORDERED, FULL, etc.) so that it works on the 1st attempt but fails on the succeeding ones. Further, if I add hints and several spaces (>3) it also works in the 1st attempt but not in the succeeding ones. Is there an explanation for that? – Markus Oct 28 '16 at 08:05
  • Hints by definition affect how the optimiser builds the plan. Getting a different plan if you run it immediately is slightly odd; maybe your stats are out of date and it's doing dynamic sampling, or something? You should look at the execution plans, and you can also trace the executions to see what actually happens on the first and second calls. – Alex Poole Oct 28 '16 at 10:01

0 Answers0