Oracle 11 - nonempty nested table reported empty when queried for size

Another addition to collection of weird Oracle 11g SQL queries. Assuming there is a empty table and user-defined type

create table tz_exp (p_id number(38,0) not null);
create or replace type rms.joedcn_number as table of number;

then following query (minimized from real query as much as I could)

with v (r_id, p_id) as (
  select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
  select v.r_id from dual join v on 0=1
  union all
  select v.r_id from dual join v on v.p_id is null
), w as (
  select cast(collect(cast(u.r_id as number)) as rms.joedcn_number) as r_ids
  from u
)
select w.r_ids
     --, (select max(column_value) from table(w.r_ids)) max_val  -- comment out this and r_ids disappears
from w

returns one row and one column with nested table, which is correct result:

+-----+
|R_IDS|
+-----+
|{123}|
+-----+

However, if we want to compute max element of collection in correlated subquery and uncomment commented row, the collection suddenly appears empty:

+-----+-------+
|R_IDS|MAX_VAL|
+-----+-------+
|{}   |null   |
+-----+-------+

(Note: question was edited, previous version of correlated subquery based on count(*)ing r_ids elements was replaceable by cardinality function and does not describe the actual problem. - thx to @MT0, see comment.)

The cause of this behaviour is very hard to hunt. My observation so far is:

• reproducible only in Oracle 11g sqlfiddle.
• tz_exp must be real table. If replaced by CTE, subquery or, say, select object_id from dba_objects where 0=1, the query works.
• v.p_id column must not be null literal, otherwise the query works
• there must be the first empty select in union, otherwise the query works

Currently we are going to migrate to Oracle 19c in near future so it is not long-lasting problem. I can workaround it on application level. I'm curious whether this is some known bug or if it is possible to workaround it on SQL level or better aim to its cause.

Initial Question link

I'm curious whether this is some known bug or if it is possible to workaround it on SQL level or better aim to its cause.

I don't know why your query is behaving as it is and it certainly appears to be a bug.

However, if you use a nested table collection (rather than a VARRAY, which is what the SYS.ODCI*LIST types are) then you can use the CARDINALITY function to count the elements in the array:

create table tz_exp (p_id number(38,0) not null);

CREATE TYPE number_list IS TABLE OF NUMBER;

Then:

with v (r_id, p_id) as (
  select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
  select v.r_id from dual join v on 0=1
  union all
  select v.r_id from dual join v on v.p_id is null
), w as (
  select cast(collect(cast(u.r_id as number)) as number_list) as r_ids
  from u
)
select w.r_ids
     , CARDINALITY(w.r_ids) cnt
from w;

Outputs:

fiddle

---

Updated question link

For the updated query, again, you can work around the error; this time by calculating the maximum in the previous sub-query factoring clause (rather than using a correlated sub-query at the end):

with v (r_id, p_id) as (
  select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
  select v.r_id from dual join v on 0=1
  union all
  select v.r_id from dual join v on v.p_id is null
), w as (
  select cast(collect(cast(u.r_id as number)) as joedcn_number) as r_ids,
         MAX(u.r_id) AS max_val
  from u
)
select w.r_ids
     , w.max_val
from   w;

Or if, per your comment, you are joining to another table then you could try using the MEMBER OF operator for filtering rather than joining to a table expression:

with v (r_id, p_id) as (
  select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
  select v.r_id from dual join v on 0=1
  union all
  select v.r_id from dual join v on v.p_id is null
), w as (
  select cast(collect(cast(u.r_id as number)) as joedcn_number) as r_ids
  from u
)
select w.r_ids
     , ( select max(some_column)
         from   some_table
         WHERE  bind_value MEMBER OF w.r_ids
       ) max_val
from w;

fiddle