Re: Re[2]: to_number question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 17 Jul 2004 13:09:45 +0100
Message-ID: <01c301c46bf6$f4cf2700$7102a8c0@Primary>

Think about this one:

create table t1 (

v1 varchar2(1),
v2 varchar2(1)
);

insert into t1 values (1,'a');
insert into t1 values ('a',1);
insert into t1 values(2,2);

commit;

select *
from t1
where

to_number(v1) = 2
and to_number(v2) = 2
;

If you handled the first two rows differently from each other (which, in effect, is what relational theory says you should do - since column ordering is not supposed to be significant), you can get the right result set. But there is no current mechanism in Oracle that could find it.

>From the relational point of view, the answer to this 'problem' is easy. You've made a mistake in your analysis and got the column domain wrong - you need a domain which includes numbers and characters and includes a "to_number" function that has meaning when applied to a character-like object in that domain.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated July 14th

Original Message ----- -----Original Message----- From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Gennick Sent: Friday, July 16, 2004 14:32 To: Stephen.Lee_at_DTAG.Com Cc: oracle-l_at_freelists.org 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

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 Sat Jul 17 2004 - 07:06:21 CDT