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

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 - 05:09:20 CDT