Subqueries in 8.1.7


I got burnt with this just recently, but its been a known bug for some time. A new feature in 8.1.7 was for the optimizer to recognise common subqueries, and thus possibly avoid repeated execution in some cases. Sounds good, but its a little dodgey in its it first cut:

create table parent ( x number);
create table child  ( y number );

insert into parent select rownum from all_objects where rownum < 20;
insert into child  select rownum from all_objects where rownum < 20;

SQL> select * from parent
  2        where (x=1 and x in (select y from child1)) 
  3           or (x=1 and x in (select y from child1)) ;

select * from parent
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

You will be "pleased" to know, that various other scenarios of the common subquery do not crash, but they just return the incorrect result set! The problem is fixed in 8.1.7.2 and in 9. The current workaround is to disable the feature:

SQL> alter session set "_eliminate_common_subexpr" = false;

Session altered.

SQL> select * from parent
  2        where (x=1 and x in (select y from child1)) 
  3           or (x=1 and x in (select y from child1)) ;

         X
----------
         1
      ...