Re: Re[6]: to_number question

I had a response crafted and just when I tried to send it my web mail client that I'm forced to use during the day lost connection - and my response in the process. I'll try again
Quoting Jonathan Gennick <jonathan_at_gennick.com>:

>
> Then I would argue that it is not I who am bringing
> something into play that violates the rules, but that SQL
> itself is doing so. I fail to see how subqueries in the FROM
> clause can fail to violate the commutativity that you speak
> of.
>

The optimizer is not violating the rules of commutativity, it uses them. You are violating them by using to expressions where the second expression relies on the successful evaluation of the first. If I boil down the query to it basic elements, then I think Stephen's query is asking for all rows in the table where cold x contains only numeric digits and, when interpreted as a number satisfy a numeric expression e.g. is greater than a constant. In an arbitrary pseudo code language:

   is_greater(is_number(col),const) = true

but by the rules of commutativity, Oracle is allowed to execute

   is_number(is_greater(col),const) = true

If you write the original query as

  select x from t
  where decode(translate(col,'0123456789a','0123456789),null,'A','N') = N   and to_number(col) > const

you immediately recognize that you may be in trouble because you know that you must not rely on the predicates being evaluated in the order they appear in the where clause. So now put the first predicate into a inline view thinking that this way you impose a order of processing. Well, you don't. The optimizer is allowed to transform your query "back" into its simpler form.

-- 
Regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

----------------------------------------------------------------
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
-----------------------------------------------------------------