select from varchar2 column with numeric value sometimes gives invalid number error

Question

I'm trying to understand why, on some systems, I get an invalid number error message when I'm trying to select a value from a varchar2 column while on other systems I don't get the error while doing the exact same thing.

The table is something like this:

ID   Column_1  Column_2
 1         V     text
 2         D        1
 3         D        2
 4         D        3

and a query:

select ID
  from table
 where column_1='D'
   and column_2 = :some_number_value

:some_number_value is always numeric but can be null.

We've fixed the query:

select ID
  from table
 where column_1='D'
   and column_2 = to_char(:some_number_value)

This original query runs fine on most systems but on some systems gives an "invalid number" error. The question is why? Why does it work on most systems and not on some?

Answer 1

This dependes on the order in which your conditions are checked. If the condition for column_1 is evaluated first, then the implicit conversion of column_2 to a number works. If column_2 is checked first, then the conversion of text to a number fails.

The optimizer decides in which order the conditions are evaluated. If you have an index on column_2 but not on column_1 for example, this index is probably going to be used, so the conversion will fail.

---

What do you mean by "some_number_value is always numeric but can be null"? The check for column_2 = NULL will return no rows, NULL can only be evaluated by using IS NULL, not = NULL.