9i ANSI outer join bug?

There appears to be a problem with ANSI outer joins and the optimizer in 9i. After gathering statistics on my tables, I get the following error on my query:

ERROR at line 1:
ORA-00928: missing SELECT keyword

I've included a demonstration script for the problem below. You should observe the first query works, then the same query fails after collecting statistics. Furthermore, a hint is shown to make the query work.

Can someone confirm this bug for me?

Thanks,
Richard Kuhler

Oracle9i Enterprise Edition Release 9.0.1.3.0 - Production With the Partitioning option
JServer Release 9.0.1.3.0 - Production

demonstration script

drop table x;
create table x (id number);
drop table y;
create table y (id number primary key, dummy_id number); create index y_i1 on y (dummy_id);
drop table z;
create table z (id number);

begin

    for i in 1 .. 1000 loop

        insert into x
        values (i);

        end loop;

    for i in 1 .. 20000 loop

        insert into y
        values (0 - i, i);

    end loop;

end;
/

select x.id
from x
left outer join y on y.id = x.id
left outer join z on z.id = y.id

    and z.id = x.id
where x.id = 1
/

exec dbms_stats.gather_table_stats(user, 'x');
exec dbms_stats.gather_table_stats(user, 'y');
exec dbms_stats.gather_table_stats(user, 'z');

select x.id
from x
left outer join y on y.id = x.id
left outer join z on z.id = y.id

    and z.id = x.id
where x.id = 1
/

select /*+ index(y) */

        x.id
from x
left outer join y on y.id = x.id
left outer join z on z.id = y.id

    and z.id = x.id
where x.id = 1
/
Received on Mon Jun 24 2002 - 15:38:13 CDT