Re[2]: to_number question

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 16 Jul 2004 15:55:42 +0200
Message-Id: <200407161355.i6GDtgDZ002261@webmail.nexlink.net>

Jonathan,
* 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 philosophy.

Stephane Faroult

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 16 2004 - 08:53:11 CDT