from the theoretical perspective, a (sub)query is a table.
a table is a set of rows, and you can specify/describe sets in several ways,
just like you can do in mathematics. for example:
- you can list/enumerate the elements, as in {2,3,5,7}
- you can describe the elements, as in {x|x is prime and x <10}
the above two sets are identical.
you see the resemblance of the second expression with a query? something
like:
SQL > select n from natural_numbers where n <10 and is_prime(n);
so a query does not result in a table, a query *is* a table.
about the issue that started this thread:
before you can even discuss meanings and correct results,
expressions should be *well-formed* in the first place.
the outcome of an ill-formed expression is undefined;
formally, you should always get an error message.
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Jonathan Gennick
Sent: Friday, July 16, 2004 14:32
To: Stephen.Lee@(protected)
Cc: oracle-l@(protected)
Subject: Re[2]: to_number question
Well put Stephen. I agree with your post.
SLDC > The comments were an expression of disbelief (not lack
SLDC > of understanding) that the specifications concerning
SLDC > subqueries would be so loose and open ended as to
SLDC > allow this level of unpredictability.
Yes. And there 's a lot more to this issue than just the
specific Oracle behavior we 've been talking about. I have
many questions, most of which I 'll just have to research
off-list. For example:
* What would relational theory have to say about this
subquery behavior? What would Codd say? What would Date say?
Do any of their writings bear directly on subquery
execution?
* What about the SQL language designers? What did they have
in mind when they spec 'd out subqueries? What 's their
conceptual vision for how subqueries execute? Did they write
anything into the standard in detail that bears on the
behavior we 've been discussing?
* It would seem that the mental model of FROM-clause
subquery results becoming the source of data for the main
query is flawed. What then, is the correct mental model to
use when thinking about and writing such subqueries? (this
last is a *very* important question)
* 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?
* Related to the above, it would now seem that in order to
write a statement containing a subquery, that you must be
aware of, and think about, how that statement 's execution is
optimized. Yet databases are supposed to free us from
worrying about optimization and physical access and the
like.
* What about other database vendors? Is this an Oracle-only
issue? How does this scenario play out under MySQL? under
DB2? under SQL Server? Is there consensus among vendors? Or
do those who design optimizers take different, fundamental
approaches to the issue we 've been discussing? (I 'd really
like to talk to the developers behind the various database
optimizers about this issue)
* Oh, and this *is* important, I haven 't had time yet to put
together a test case that reproduces Stephen 's error. It 's
on my to-do list though. My one attempt so far failed to
reproduce the error. If I can 't put together a test case,
then I have nothing to research.
Now that I see what Oracle is doing in this one case, I want
to go deeper, find out about the intent (of theorists,
language designers, optimizer writers), come up with a
mental model that encompasses the behavior we 've seen, and
so forth. There 's a lot of drilling down to be done here,
and probably a good article to be written afterward.
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@(protected)
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit
http://five.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request@(protected) and
include the word "subscribe " in either the subject or body.
Friday, July 16, 2004, 3:59:11 AM, Stephen.Lee@(protected)
(Stephen.Lee@(protected)) wrote:
> > -- --Original Message-- --
> > Actually the subquery gets converted to a sql that has two predicates
> > grouped by "AND " (similar to yours).
SLDC > Well OK. I 've kept my mouth shut so far, and because I was getting
some
SLDC > good info, I did not argue with the accusations that I "don 't
understand ".
SLDC > But I think it 's time to clarify some things.
SLDC > I DO understand 100%, and (if I may be so bold as to speak for others)
those
SLDC > who have questioned the so-called logic understood 100%.
SLDC > Yes. I 'm very well aware that my SQL was essentially getting broken
into
SLDC > two predicates and THEN the un-guaranteed order in the evaluation of
SLDC > predicates gets applied. The people who argued that this should not
be the
SLDC > case understood it too. The comments were an expression of disbelief
(not
SLDC > lack of understanding) that the specifications concerning subqueries
would
SLDC > be so loose and open ended as to allow this level of unpredictability.
SLDC > Clearly, that is the case. But that doesn 't mean we can 't bitch about
it.
SLDC > And bitching about it doesn 't mean we don 't understand it. You bitch
about
SLDC > taxes, don 't you?
SLDC > -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
SLDC > Please see the official ORACLE-L FAQ: http://www.orafaq.com
SLDC > -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
SLDC > To unsubscribe send email to: oracle-l-request@(protected)
SLDC > put 'unsubscribe ' in the subject line.
SLDC > --
SLDC > Archives are at http://www.freelists.org/archives/oracle-l/
SLDC > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
SLDC > -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
