| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Bug with "NOT IN" subqueries
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
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
 |
 |