Weird ORA-00904 invalid identifier in query with nested CTEs and collections

Question

Following query fails on ORA-00904. Why?

with t (id, parent_id) as (
  select 1, 2 from dual
)
, wrapper (id, parent_id) as (
  select * from t where parent_id = 2
)
, wrapper_with_elements (id, elements) as (
  select u.id, sys.odcinumberlist(1) as elements
  from wrapper u
)
select (
         select cast(collect(cast(ru.id as number)) as sys.odcinumberlist)
         from wrapper_with_elements ru
       ) as agg1
     , (
         select cast(collect(cast(ru.id as number)) as sys.odcinumberlist)
         from wrapper_with_elements ru
       ) as agg2
from wrapper w

ORA-00904: "PARENT_ID": invalid identifier

Db fiddle for Oracle 11g, however it works same way even on newest Oracle 21c version.

The query comes from more complex practical query and after minimization it became somewhat nonsense. However any attempt of further simplification makes error disappear, namely:

• replacing any or both from wrapper for from t as it is same data
• replacing sys.odcinumberlist(1) for null or any scalar value
• inlining wrapper_with_elements into from clause in correlated subqueries of main query
• removing any of agg1, agg2 columns

Does someone has an idea what is going wrong there? Although the query is silly I would expect it to return value.

I suspect this is some Oracle bug related to collection usage (because once I managed to make some workaround run without bind variables but with bind variables it failed with ORA-600: Internal Error Code, Arguments: [qcsfbdnp:1] which disappeared only after collection handling removal) but I don't want it to be treated as misleading conclusion. In real case I use custom nested table type but the effect is the same, I used sys.odcinumberlist just to make example easily reproducible.

Answer 1

The problem could be declaring the same column names lists twice in two cte definitions.
t (id, parent_id) as
and
wrapper (id, parent_id) as

If you change the way of naming of columns in just your first cte - so that you give the names of columns within the Select statement instead as a list of columns outside then there is no error.

with t as (
  select 1 as id, 2 as parent_id from dual
)

... it is unknown to me how Oracle treats those column names lists declared outside Select statement and how they are (the column names) assigned to a particular CTE. That is problematic in case of multiple CTEs.
Regards...