Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 10 Jul 2002 13:27:45 -0700
Message-ID: <agi5c1024j3@drn.newsguy.com>


In article <3d2b3ba3_1_at_mk-nntp-1.news.uk.worldonline.com>, "Paul says...
>
>Could anyone enlighten me please?
>I ran into this behaviour today, and a search on metalink indicated
>that apparently it is expected.
>
>BTW: 8.1.7.0 on HPUX (and I understand many other versions).
>
>select count(*) from emp where empno in (select empno from dept);
>/* Of course, DEPT has no EMPNO column */
>
>I think this should raise an exception, but instead it returns
>a count of the rows in EMP, apparently because although EMPNO is not
>a column in the DEPT table, it is within scope for the query,
>since a column with this name exists in the EMP table,
>thus we are effectively saying 'where EMP.EMPNO = EMP.EMPNO'
>
>I find it difficult to buy this, since however Oracle is going to
>merge the subquery or optimise the plan, as far as I can see,
>conceptually it should do this:
>
>1) Evaluate the result of the subquery, returning one of:
>
> an exception
> no rows
> one or more rows
>
>2) Pass this result to the outer query as a list of values for the
>predicate.
>
>In other words, IMHO the subquery should behave as if it were
>self-contained, without reference to the outer query.
>Since the subquery is not in and of itself valid,
>the whole thing should fail.
>
>There may well be issues of which I am unaware, and I am sorry
>if this is an old one, but I would appreciate input on this,
>since what Oracle appear to be saying (on metalink) appears to me
>to be totally counter-intuitive.
>
>Thanks in advance,
>Paul
>
>
>

see

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:451220136504

for a write up on this correct behavior.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Wed Jul 10 2002 - 15:27:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US