Re: Bug in Oracle 7.3

Bjørn Pedersen 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.

What's wrong? Did you read the specifications for SQL? The result is ok because fld1 is defined in the subquery. It must be, because elsewhere you could not built interesting constructs like:

SELECT fld1,fld2 from tab1 where fld1 in (select fld3 from tab2 where fld4=fld2);

-- 
----------------------------------------------------------
           IMSoft - Dipl.-Ing. Michael Karg
             Graf Starhemberggasse 32/2/3
                      1040 Wien
                       Austria
----------------------------------------------------------
e-mail   : karg_at_uranus.tuwien.ac.at
           michael.karg_at_bigfoot.com
           +436641015732_at_text.mobilkom.at
Tel & Fax: ++43/1/5056190
A1       : ++43/664/1015732
WWW      : http://uranus.tuwien.ac.at/~karg/imsoft.html
           http://titania.tuwien.ac.at/~karg/imsoft.html