SQL statement bug/puzzle in 9iR2?

[lengtai]

Platform : Windows, Unix
Oracle version: 9.2.0.1 and 9.2.0.4

I encountered a SQL query puzzle in 9iR2.

Create a default DB with 'optimizer_mode = CHOOSE' and try this:

drop table t1;

create table t1 (f1 varchar(10),
f2 varchar(10),
f3 varchar(10));

insert into t1 values ('1','5','3');
insert into t1 values ('4','4','6');
commit;

Next, execute this query,

SELECT f1,f2,f3
FROM t1 a
WHERE a.f2 <= '5'
AND NOT EXISTS (SELECT '1'
FROM t1 b
WHERE b.f2 <= '5'
AND b.f2 > a.f2);

expected result:

F1 F2 F3
---------- ---------- ----------
1 5 3

Now, analyze the table t1 with compute statistics
SQL> analyze table t1 compute statistics;

Execute the same query again,

You will get no rows return.

Delete the statistics on table t1 and execute the same query, you will get the same expected one row!

begin
dbms_stats.delete_table_stats(ownname=> 'JDOE', tabname=> 'T1');
end;

By the way, if the optimizer_mode is set to 'rule', the expected result is correct which is one row.

In oracle 8i, the query return exactly one row regardless whether the table is analyzed or not.

Anyone has any explanation for this?

Thanks.

[mdash]

yes u are right . I have tested this one .
it's surprised me .How output is realted to statistics?

[stancho]

It is something with the execution plan, in cost based it is

Operation Name Rows Bytes Cost
SELECT STATEMENT 2 8 6
NESTED LOOPS ANTI 2 8 6
TABLE ACCESS FULL T1 2 6 2
TABLE ACCESS FULL T1 1 1 2

and with rule it is

Operation Name
SELECT STATEMENT
FILTER
TABLE ACCESS FULL T1
TABLE ACCESS FULL T1

And if someone says that CBO is better than RBO again

[jmodic]

Known bug. Bug# is 2723141. You can find it on Metalink, the bug title is "QUERY RETURNS INCORRECT RESULTS WITH NOT EXIST AND SUB QUERY THAT RETURNS 0 ROWS".

It is supposed to be fixed in 10.0. So apparently there is no patch available for any 9i release!