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

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 Received on Tue Jul 09 2002 - 13:52:09 CDT