Oracle doesn't comply to basic SQL Standards

In June 1994, I found out, a simple but kernel generic bug. It has been reproduced by Oracle Base Dev team, and should be fixed in 7.3.2 (in rule cost mode only, no patch), can someone test it
and tell me if it is really fixed in 7.3.2 (it is not in 7.3.1)

I know that there is a simple workaround, don't waste your time to find it out.

Contact me if you want to know why it is so long to correct a kernel bug.

Here is one of the faxes I sent to Oracle France :

/* June, 22 1994

ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK BUG on all Oracle versions (tested on V7 Netware, V6 SUN, V6 Dos standalone, V7.0.16.4 OS/2)
Whatever parser optimizer mode

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

Works on Gupta SQLBase

*/

/* for char -> varchar2 on V7 : */
Set compatibility V6

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 not return an error

It seems that Oracle transform this query like this :

select nmbr from numbers where not exists (select 1 from IntNumbers where value is not null and nmbr is not null and
(kind='POSITIVE INT' or kind='NEGATIVE INT')
and nmbr=1/value

which fails.

there is a simple Workaround for the same (from Oracle US base dev) : select nmbr from numbers where not exists (select 1 from IntNumbers where nmbr=1/value and value is not null and nmbr is not null and
(kind='POSITIVE INT' or kind='NEGATIVE INT')

The change in the kernel doesn't seem very difficult, why is it so long ?

There is the same bug in a statement like that : delete from table1 where key1 not in (select to_number(column2) from Table2 where key2 like 'FOO%')

The same workaround applies.

• Laurent PELE | Tel: +(33) (1) 42 29 93 58 13 rue Lantiez | Fax: +(33) (1) 42 29 99 32 F- 75017 PARIS | lpele_at_worldnet.fr http://www.worldnet.net/~lpele/
• Open an account in E-cash and visit my own picture shop on http://www.worldnet.net/~lpele/images.html, that's cheap!