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

"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message news:<3d2b3ba3_1_at_mk-nntp-1.news.uk.worldonline.com>...
> 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.
>

This stuff can be very confusing. The fact is that columns involved in a subquery are within the scope of the main query. In your query Oracle first looks in the dept table and doesn't find empno. It then looks in the emp table, finds it there and uses it in the subquery.

What you were thinking is a query like this: select count(*) from emp where empno in (select dept.empno from dept); It should give you a syntax error.

> 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
Received on Wed Jul 10 2002 - 00:44:09 CDT