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 !