Re: ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK

And your point is.....? :-)

laurent_pele_at_my-deja.com wrote:

> ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK
>
> BUG on all Oracle versions including 8.0.5.0.0 Oracle Server on Unix
>
> I found it in July 1994, yes 1994 ! tested on V7 Netware, V6 SUN, V6 Dos
> standalone,
> it was supposed to be corrected about 2 years after on a 7.3.x version.
> But now, there is still a problem on a 8.0.5.0.0 Oracle unix server.
> I also tried on a 7.3.2.3.0 Oracle Unix Server (but this version was not
> supposed to be fixed)
>
> Bug on SubQueries with NOT IN operator when Full Table Scan on
> SubQueries
> it evaluates return value when it doesn't match to the Where Clause
> and generates an error when some values are not Allowed
> (you can also have such a bug if you use to_number function applied to a
> column where
> there are not numbers in other rows than the selected one)
>
> Why is it so long to repair such standard SQL bugs ???
>
> Drop table Numbers;
> Create Table Numbers(nmbr number(20,4));
>
> Drop Table IntNumbers;
> Create Table IntNumbers(
> Kind char(20),
> Value Number(10));
>
> Insert into Numbers (nmbr) values(0.1);
> Insert into Numbers (nmbr) values(0.5);
> Insert into Numbers (nmbr) values(-0.125);
>
> insert into IntNumbers (Kind,Value) values ('NEGATIVE INT', -8);
> insert into IntNumbers (Kind,Value) values ('POSITIVE INT', 10);
> insert into IntNumbers (Kind,Value) values ('ZERO', 0);
>
> commit;
>
> select 1/value from IntNumbers Where Kind='POSITIVE INT' or
> Kind='NEGATIVE INT';
> REM Return No Error and two lines
>
> select nmbr from Numbers where nmbr in
> (select 1/value from IntNumbers Where Kind='POSITIVE INT' or
> Kind='NEGATIVE INT') ;
> REM Return No Error and two lines
>
> select nmbr from Numbers where nmbr not in
> (select 1/value from IntNumbers Where Kind='POSITIVE INT' or
> Kind='NEGATIVE INT') ;
> REM return Error 1476 divisor is equal to zero and return no lines
> REM should return one line and no error ???
>
> REM ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES
> DOESN'T WORK
>
> --
> Laurent PELE mailto:laurent_at_pele.org
> http://www.pele.org
> Online currency converter on http://195583.com
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Sep 23 1999 - 09:42:46 CDT