Re[2]: to_number question

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 16 Jul 2004 15:55:42 +0200

* Clearly we have a case where an optimization changes the
behavior of a statement. Without optimization the statement
succeeds. With optimization the statement fails. Even though
I understand what is happening, this troubles me. It raises
an interesting question: should an optimization be allowed
to change the results? Why/why not?

On this point alone I wouldn't say that optimization changes the result. We
have here a 3-valued logic again :
- expected results
- wrong results
- failure

What would REALLY worry me is the possibility of getting wrong results.
Failure is something different. However disturbing it may be to see a query
which used to give the expected result fail because of a sudden execution
plan change, I don't consider that this is any more serious than, say,
ORA-1555 - Currently your call cannot get through, please try again later.
Failure results from some more or less serious misuse of the database, and
success (so far) was just a result of the magnanimity of Oracle, which will
give you a result if it can give you the good one. I have long been puzzled
by the reason why, when doing an implicit say char to number conversion,
Oracle was choosing to convert the character column to number instead of
converting the number constant to char - conversion to char often looking
like the safe bet (besides making the index usable).
In fact, if you type char_error_number = 942 you probably expect '00942' to
be returned - which the Oracle behaviour does, and wouldn't have been the
case otherwise. And if char_error_number contains '*', tough luck - Oracle
tried its best, but your query was flawed, anyway. Forgiving, but to some
limited extent.
It looks to me like the case raised by Stephen participates in the same

Stephane Faroult 

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: