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

Post by laurent_p.. » Sun, 31 Dec 1899 09:00:00



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

--

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.

 
 
 

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

Post by Win Ke » Sun, 31 Dec 1899 09:00:00


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

> 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

> --

> 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.


 
 
 

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

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


The expression you are looking for is:

    "There is a bug in the rule-based optimiser
     which causes some SQL statements to fail."

Fortunately this was fixed in the cost based
optimiser quite a long time ago.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>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,

 
 
 

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

Post by Vince Cros » Sun, 31 Dec 1899 09:00:00



> The expression you are looking for is:

>     "There is a bug in the rule-based optimiser
>      which causes some SQL statements to fail."

> Fortunately this was fixed in the cost based
> optimiser quite a long time ago.

> --

> Jonathan Lewis

Apparently it wasn't fixed completely.  I have the same bug in 8.0.5.2.0
whether I use RBO or CBO.  Example:

Table USER_INFO: userid VARCHAR2(12) PRIMARY KEY

Table MILESTONE: (job_key NUMBER, milestone_ID VARCHAR2(4)) PRIMARY KEY
                 resp_eng VARCHAR2(12) REFERENCES user_info(userid)
                 update_eng VARCHAR2(12) REFERENCES user_info(userid)

SQL> SELECT * FROM user_info WHERE userid NOT IN (SELECT resp_eng FROM
milestone);

no rows returned

The same result is returned if I use the "update_eng" field for the
subquery.  I know for a fact that at least 20 rows should be returned
from this query.  It doesn't matter which OPTIMIZER_MODE is used or if
statistics exist on the table.  

There are 4 other child tables that have a FK reference to "userid" in
table USER_INFO.  This query works fine for them.  I have also tried
dropping various indexes, FK constraints, etc. to see if that changed
the results.  It still fails.  I'm going to open a TAR today as this is
a rather serious bug.

If anybody has any ideas, please let me know.

Thanks,

Vince

 
 
 

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

Post by Thomas Kyt » Sun, 31 Dec 1899 09:00:00



(if that email address didn't require changing)



>> The expression you are looking for is:

>>     "There is a bug in the rule-based optimiser
>>      which causes some SQL statements to fail."

>> Fortunately this was fixed in the cost based
>> optimiser quite a long time ago.

>> --

>> Jonathan Lewis

>Apparently it wasn't fixed completely.  I have the same bug in 8.0.5.2.0
>whether I use RBO or CBO.  Example:

>Table USER_INFO: userid VARCHAR2(12) PRIMARY KEY

>Table MILESTONE: (job_key NUMBER, milestone_ID VARCHAR2(4)) PRIMARY KEY
>                 resp_eng VARCHAR2(12) REFERENCES user_info(userid)
>                 update_eng VARCHAR2(12) REFERENCES user_info(userid)

>SQL> SELECT * FROM user_info WHERE userid NOT IN (SELECT resp_eng FROM
>milestone);

>no rows returned

this is not a bug.  You simply have at least one row with resp_eng is NULL.
Consider:


Table created.


Table created.


T1_ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.


1 row created.


no rows selected

All you need is to have one row with a NULL and the NOT IN returns 'UNKNOWN' --
not true, not false -- UNKNOWN.

Try:

SELECT * FROM user_info WHERE userid NOT IN (SELECT resp_eng FROM milestone
where resp_eng IS NOT NULL );

or even better:

SELECT *
  FROM user_info
 WHERE NOT EXISTS
    ( select null from milestone where milestone.resp_eng = user_info.userid );

that'll be much faster if there is an index on resp_eng in milestone.

- Show quoted text -

Quote:

>The same result is returned if I use the "update_eng" field for the
>subquery.  I know for a fact that at least 20 rows should be returned
>from this query.  It doesn't matter which OPTIMIZER_MODE is used or if
>statistics exist on the table.  

>There are 4 other child tables that have a FK reference to "userid" in
>table USER_INFO.  This query works fine for them.  I have also tried
>dropping various indexes, FK constraints, etc. to see if that changed
>the results.  It still fails.  I'm going to open a TAR today as this is
>a rather serious bug.

>If anybody has any ideas, please let me know.

>Thanks,

>Vince

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

 
 
 

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

Post by Vince Cros » Sun, 31 Dec 1899 09:00:00


DOH!  I can't believe I didn't figure that out.  You'd think after all
of these years, I would have run into this one before.

Thanks for you help, especially since it saved me from opening a TAR on
it.

Vince


> this is not a bug.  You simply have at least one row with resp_eng is NULL.
> Consider:

(posting trimmed)


> no rows selected

> All you need is to have one row with a NULL and the NOT IN returns 'UNKNOWN' --
> not true, not false -- UNKNOWN.

> Try:

> SELECT * FROM user_info WHERE userid NOT IN (SELECT resp_eng FROM milestone
> where resp_eng IS NOT NULL );

> or even better:

> SELECT *
>   FROM user_info
>  WHERE NOT EXISTS
>     ( select null from milestone where milestone.resp_eng = user_info.userid );

> that'll be much faster if there is an index on resp_eng in milestone.

 
 
 

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

Post by laurent_p.. » Sun, 31 Dec 1899 09:00:00




Quote:

> The expression you are looking for is:

>     "There is a bug in the rule-based optimiser
>      which causes some SQL statements to fail."

> Fortunately this was fixed in the cost based
> optimiser quite a long time ago.

That's right that there is no error if the optimizer mode is forced to
cost based mode and if I add an analyse table statement.

So, the corrected SQL script should be :

/*
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 ???
*/

Alter session set optimizer_mode=choose;

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);

REM Generate an error 1476 (divide by 0) if you remove the 2 following
analyze statements :

Analyze Table IntNumbers compute statistics;

Analyze Table Numbers compute statistics;

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 Works because of the analyse statement
REM return NO Error 1476 divisor is equal to zero and return no lines
REM should return one line and no error ???

Drop table Numbers;
Drop Table IntNumbers;

--

http://www.pele.org
Online currency converter on http://195583.com

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

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

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


I would guess that the problem is actually
a fairly subtle one in the optimiser folding
code.

Since there is an obvious work-around for
any particular case (even though it may
add quite an overhead to large scale
queries), and no-one can report it as
a level-1 problem it always gets pushed
to the end of the queue.

And now, of course, it's only in the RBO,
and due to be retired asap.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>That's right that there is no error if the optimizer mode is forced to
>cost based mode and if I add an analyse table statement.

>So, the corrected SQL script should be :

>/*
>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 ???
>*/

 
 
 

1. Query works in SQL Query Analyzer but doesn't work in DTS

I am trying to insert records to a table in a database if the records are not existed in the table already. The source data is from a table in another database.  My query runs fine when I test it in Query Analyzer but it doen't seem working in DTS package. I setup a job to run a DTS package nightly. The job runs no error. But it doesn't insert the records. Any thougts and suggestions are greatly appreciated.  ---YH

Here is the my query:

INSERT INTO [db1]..[Members_]
   (UserID_, Domain_, EmailAddr_, FullName_, List_, UserNameLC_)

SELECT Distinct
        [db2]..[mgt505-01].comment,
        [db2]..[mgt505-01].Domain,
        [db2]..[mgt505-01].email,
        [db2]..[mgt505-01].fullname,
        [db2]..[mgt505-01].list,
        [db2]..[mgt505-01].namelc

FROM [db2]..[mgt505-01], [db1]..[Members_]
     WHERE ([db2]..[mgt505-01].[Email]
        NOT IN
                (SELECT [db1]..[Members_].[EmailAddr_]
                FROM [db1]..[Members_]
                WHERE [db1]..[Members_].[List_] = 'mgt505-01')
                 And [db1]..[Members_].[list_] = 'mgt505-01')

2. Foreign Keys -- Improve performance??

3. Scheduling DTS doesn't fail but doesn't work

4. Result is NULL???

5. simple query doesn't work for me

6. Is there a way to use rand() with a seed value?

7. Pretty simple select query doesn't work???

8. simple, simple SELECT doesn't work with me in SQL7

9. Simple, simple SELECT doesn't work

10. Oracle's JDBC driver's PreparedStatement.cancel() doesn't work

11. Query built in Access doesn't work in SQL Server

12. SQL 2000 Query Analyzer Debugger doesn't work