Re: Re[2]: to_number question

Quoting Jonathan Gennick <jonathan_at_gennick.com>:
>
> SELECT a, to_number(b)
> FROM ( SELECT a, b
> FROM subtest
> WHERE a = 'a' );
>
> I admit that I have not gone back to the ANSI standard to
> read in detail what it says about this particular case.
> However, I have a very difficult time understanding how you
> can look at the above query and come up with any other
> explanation of it than the following:
>
> 1. The subquery defines a *new* table of rows.

No, it does not. That same query can be written in a semantically equivalent way without the subquery. And you rightlfully expect the optimizer to "effortlessly" convert your query into all of them and pick the one that returns the result the festest.
Subqueries are a means to structure a query such that its logic is more easily grasped. They are NOT a means to impose a processing order.
>

> Is there really something in the ANSI standard that says
> otherwise? Does relational theory really allow the outer
> query to execute against rows that are not in the "table"
> I've listed in my FROM clause?

Yes. You are under a misconception about what the table in your from clause is. It is ALWAYS the base physical table(s). ANY row in that table may be accessed in ANY order and subjected to the projection, filter(s), and join(s).

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