Bug with "NOT IN" subqueries

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

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 Received on Thu Sep 30 1999 - 12:33:11 CDT