Re: Bug in Oracle 7.3 -- NOT --
Date: 1997/04/08
Message-ID: <334a4c2a.1511663_at_newshost>#1/1
This is *not* a bug. This is the expected result from any SQL database.
You have just run a correlated subquery :)
This will happen in all databases. The Fld1 in the query:
>
>select fld1,fld2 from tab1
>where fld1 in (select fld1 from tab2);
>
in the inner query is coming from (as it is supposed to) the outer query.
The scoping of fld1 in the above query is such that it is visible to all subqueries below it.
You generally use it as such:
select fld1, fld2 from tab1
where exists ( select X from tab2 where tab2.some_field = fld1 )
/
On 8 Apr 1997 09:52:11 GMT, "Bjørn Pedersen" <bjoped_at_online.no> wrote:
>-- bjp_at_ssb.no
>--
>-- Serious BUG in Oracle 7.3:
>-- An example:
>
>drop table tab1;
>create table tab1 (fld1 number,fld2 number);
>drop table tab2;
>create table tab2 (fld3 number,fld4 number);
>
>insert into tab1 values(1,1);
>insert into tab1 values(1,2);
>insert into tab1 values(1,3);
>insert into tab1 values(2,1);
>insert into tab1 values(2,2);
>insert into tab1 values(2,3);
>
>insert into tab2 values(1,1);
>insert into tab2 values(1,2);
>insert into tab2 values(1,3);
>
>commit;
>
>select fld1,fld2 from tab1
>where fld1 in (select fld1 from tab2);
>
>-- This statement will work even if tab2 only contains fld3 and fld4
>-- because fld1 is found in tab1. The result will be erronous, because
>-- all rows from tab1 will be selected. This behaviour could give very
>-- strange results, if you by mistake includes a column-name from tab1
>-- in the second select. This error must be considered a serious bug.
>
>select fld1,fld2 from tab1
>where fld1 in (select fld5 from tab2);
>
>-- Will produce an error (as i rightly should).
>
>select fld1,fld2 from tab1
>where fld1 in (select fld3 from tab2);
>
>-- Will give the desired result.
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Apr 08 1997 - 00:00:00 CEST