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 !