Kernel RDBMS Bug on a simple query (V6, V7, all platforms)
Date: Sun, 12 Feb 1995 01:48:47 GMT
Message-ID: <lpele.4.2F3D68FF_at_world-net.sct.fr>
Oracle doesn't comply to basic SQL standards. I've found out a bug on June, the 22nd of 1994 and I'm still waiting for an upgrade.
The bug appears on simple Select queries with a "not in" subquery and using a function that can return an error (like LOG, TO_NUMBER or division operator) :
I've tested it on V7.0 Netware, V6 SUN, V6 Dos standalone, V7.0.14 OS/2... It works on Gupta SQL*Base
Here is a short example :
Set compatibility V6;
REM (to change char in varchar2 on V7 create table)
Create Table Numbers(nmbr number(20,4));
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 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
Oracle Support told me that it would me corrected in V7.1.3, can anybody
test this query on this release ?
When will this release be available on OS/2 or Windows servers ?
Is there a mean to desactivate optimizer and avoiding the error
Laurent PELE
13 rue Lantiez
75017 PARIS
FRANCE
tel + (33) 1 42.29.93.58
E-mail: lpele_at_world-net.sct.fr
High wall thickness doesn't count as much as the strength of will to clear it (Thucydide, 420 BC)
Laurent PELE
13 rue Lantiez
75017 PARIS
FRANCE
Tel: +33 1 42 29 93 58
Email: lpele_at_world-net.sct.fr
L'epaisseur d'une muraille compte moins que la volonte de la franchir Thucydide, in La guerre du Peloponese vers -420 av JC
Received on Sun Feb 12 1995 - 02:48:47 CET