Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Bug with "NOT IN" subqueries

Re: Bug with "NOT IN" subqueries

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Sep 1999 14:55:42 -0400
Message-ID: <arHzN9UPyYiNs=upvmby7hfTCHID@4ax.com>


A copy of this was sent to Vince Cross <bartok_at_nortelnetworks.com> (if that email address didn't require changing) On Thu, 30 Sep 1999 12:33:11 -0500, you wrote:

>
>
>Jonathan Lewis wrote:
>>
>> 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:

tkyte_at_8.0> create table t1 as select ename t1_ename from scott.emp; Table created.

tkyte_at_8.0> create table t2 as select ename t2_ename from scott.emp where 1=0; Table created.

tkyte_at_8.0> select * from t1 where t1_ename not in ( select t2_ename from t2 );

T1_ENAME



SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER 14 rows selected.

tkyte_at_8.0> insert into t2 values ( NULL); 1 row created.

tkyte_at_8.0> select * from t1 where t1_ename not in ( select t2_ename from t2 ); 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.

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

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 30 1999 - 13:55:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US