Wrong Results/No Rows for Sql Involving Functions in 11.2.0.2. (Doc ID 1380679.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

In 11g a query containing both a function and TABLE function returns no rows.
Consider the following testcase:

create table talx(n number);
insert into talx values (1);
create type yalx as object (a number);
/
create type tyalx as table of yalx;
/
create or replace function falx(p number) return tyalx pipelined as
begin
if (p is not null) then
pipe row(yalx(p));
pipe row(yalx(p*p));
end if;
return;
end;
/

The following query gives wrong results returning  no rows instead of correctly returning 2 rows:

select tb.a from (select falx(n) fn from talx) iv, table(iv.fn) tb;



Wrong result plan:

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 119K| 27 (0)|
| 1 | MERGE JOIN CARTESIAN | | 8168 | 119K| 27 (0)|
| 2 | TABLE ACCESS FULL | TALX | 1 | 13 | 3 (0)|
| 3 | BUFFER SORT | | 8168 | 16336 | 24 (0)|
| 4 | COLLECTION ITERATOR PICKLER FETCH| FALX | 8168 | 16336 | 24 (0)|
---------------------------------------------------------------------------------

Changes

Upgraded to any version of 11g

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms