SQL on Object types


A bizarre result when using SQL against object table collections. First consider the following example coded up to find similar values in two collections, where "TABLE(CAST(" is used so that a standard SQL with a "WHERE .. IN" clause can be used to join the two collections (p1 and p2) together.


create type a_record is object (
  num_val  number,
  char_val varchar2(10) );
/

create type a_array is table of a_record;
/

declare
  p1 a_array := a_array();
  p2 a_array := a_array();
begin
  p1.extend(4);
  p1(1) := a_record(1,'a');
  p1(2) := a_record(2,'a');
  p1(3) := a_record(3,'a');
  p1(4) := a_record(4,'a');

  p2.extend(3);
  p2(1) := a_record(1,'b');
  p2(2) := a_record(3,'b');
  p2(3) := a_record(4,'b');

  for i in ( 
     select *
     from   table(cast(p1 as a_array))
     where  num_val in 
       ( select num_val
         from table(cast(p2 as a_array))
       )
          ) loop
   dbms_output.put_line(i.num_val);
  end loop;
end;
/
The output is as expected, namely NUM_VAL's of 1,3,4 (namely they are present in both "p1" and "p2") are output. You would imagine then that replacing the "WHERE .. IN" with a "WHERE EXISTS" should yield the same result. However if the code above is replaced with a
     select *
     from   table(cast(p1 as a_array)) p
     where  exists 
       ( select null
         from table(cast(p2 as a_array))
         where num_val = p.num_val
       )

then I got all four values as a result !