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

I continue a thread started 24 november 1995 (see google archive) and continued on 23/09/1999

Could anyone try the following queries and tell me if the bug is still appearing in the RBO. That bug was suppose to be fixed in 1999-2000 (I received at that date a version without the problem). It doesn't appear in the Cost based optimizer, only in Rule based mode

but I tried on Oracle 9.2.0.5.0 and the problem still occurs. And I heard that RBO should not be support anymore in Oracle 10 (but they already said that in 1999)

ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK 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)

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 so Oracle doesn't comply to basic sql standard

REM Fortunately, there is a workaround in Rule Based Mode : select nmbr from Numbers where not exists (select 1 nmbr from IntNumbers where nmbr=1/value and (Kind='POSITIVE INT' or Kind='NEGATIVE INT')) ;
REM return one line and no error ! Received on Thu Nov 03 2005 - 05:17:38 CST