Create materialized view started breaking in 19.13. 19.14 is affected as well.

In contrast, the issue doesn’t appear in RUs until up to 19.12 and in 21.4.

The test case:

create table t1 (n1 number) ;

create table t2 (n1 number, c1 varchar2(4000)) ;

create table t3 (n1 number, n2 number);

create table t4 (n1 number);

create or replace force view v1 ( n1, c1, n2 )
as
  with
    cte_in_view as
      (select n1 from t1 )
  select t2.n1, t2.c1, 1
    from t2 
      left join cte_in_view c on c.n1 = t2.n1   
;

create materialized view mv
as
  with
    cte_in_view as
      (select v1.n1, v1.c1 
        from v1
          left join t3 on t3.n1 = v1.n2
          left join t4 on t3.n2 = t4.n1
        ),
    cte2 as
      (select n1, c1
         from cte_in_view
)
  select 1 as n1
    from cte2 cte2_1
         left join 
           cte2 cte2_2
           on     cte2_1.c1 = cte2_2.c1
       left join 
         (   with
    cte_recursive (n1) 
    as
      (select 1 as n1 
         from dual
       union all 
       select n1 + 1
         from cte_recursive
         where n1 <=5 
        )
  select n1
    from cte_recursive
) v2 on v2.n1 = cte2_1.n1 ;

The error message is

ORA-00904: "C1": invalid identifier

There’s nothing wrong with the identifier C1. If we replace

create materialized view mv

with

create table t

the command runs without errors.

The following conditions seem to lead to the problem:

• common table expressions (CTE) with the same name appear in different views
• multiple layers of nested CTEs (like babushka dolls)
• recursive subqueries

You can try changing any of the conditions above as a workaround. For example, if you rename cte_in_view, the query will run without errors.

The other options are:

• falling back to 19.12
• upgrading to 21c

I have tried flipping the configurable bug fixes (“_FIX_CONTROL”) that appeared in 19.13, but to no avail.

Update on January 28, 2022

Oracle Support filed the new bug:
Bug 33797784 : MATERIALIZED VIEW FAILS WITH ORA-00904 ON 19C DATABASE