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

Paul Brewer wrote:
>
> 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

Its easy enough to see where this would cause confusion, but the construct (ie searching 'parent' tables for column names) is required for correlated subqueries eg

select * from tableA
where col in ( select ... from tableB where tableA.other_col ... )

The moral is - always associate a table alias in queries that use more than one table. I haven't tested it but I would not be surprised if doing improves the parse time as well.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."