1

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.

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64
  • If it looks like a bug (and I'd say it does) then you probably need to raise a service request with Oracle. To add to your list of things that make the error disappear, changing the final outer `from wrapper w` to `from dual` also works, as the query doesn't refer to it; which probably isn't helpful for your real query. I'd guess the optimiser's query transformation is inlining something inconsistently - tracing it might show what? - but not sure if knowing will help either. Oracle might be able to suggest a workaround, disabling an optimiser feature perhaps. Or a hint. – Alex Poole Oct 21 '22 at 14:43

1 Answers1

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...

d r
  • 3,848
  • 2
  • 4
  • 15
  • It isn't the names being repeated - the same thing happens if the two CTEs have different column aliases. But it's very interesting that it works if you alias the columns in the query itself - with or without removing the outer aliases (though the aliases have to be the same if both present!?). Something weird is definitely happening with the aliases - and that still looks like a bug. Good spot, and I'm sure a useful workaround. – Alex Poole Oct 21 '22 at 17:12
  • Yeah, it probably is a bug, but a bug or not - I just avoid declaring aliases outside the query. – d r Oct 21 '22 at 17:29
  • I confirm it helped. I tracked suggested change to original example where `wrapper` is actually recursive query and it ultimately worked with column list in parentheses in recursive CTE declaration and column name declarations in `select` clause in non-recursive CTEs. The occurence of collections seems to have no influence. Thanks. – Tomáš Záluský Nov 02 '22 at 23:24