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.
>
> 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
>
>

I wonder what you really wanted to achieve with that query...

Something like this ?

select count(*) from emp a
 where exists (select 'EUREKA!' from dept

                         where empno = a.empno);

This query will CERTAINLY fail at parse time if no empno column exists in the dept table and it will return the count of all empno present in both tables in the other case.

I mean, the original query is simply unfit for the purpose apparently, it's not a matter of how Oracle handles column names.

The parser is designed to return syntax errors, not code design errors.

Bye,
Flavio Received on Wed Jul 10 2002 - 15:44:41 CDT