Team,

demo@ATP19C> create table t( x int primary key, y int );

Table created.

demo@ATP19C> insert into t(x,y)
  2  select 20+rownum, rownum
  3  from all_users
  4  where rownum <= 10;

10 rows created.

demo@ATP19C> declare
  2     l_key number ;
  3  begin
  4     update ( select t1.x, t1.y, t2.new_y
  5             from t t1, ( select x, mod(y,3) as new_y
  6                             from t
  7                             where rownum > 0 ) t2
  8             where t1.x = t2.x )
  9     set y = new_y
 10     where y <> new_y
 11     returning max(x) into l_key ;
 12     dbms_output.put_line( ' key => '|| l_key );
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
ORA-06512: at line 4

demo@ATP19C> select banner_full from v$version;

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.1.0

we got a requriment to update some records in a table and return the someother values from it.
we can't use "merge" since it dont support the "returning" clause.
so we ended up using the update statement, but we ended up with ORA-01779 error in 19c(19.18)
the inline-view aliased "T2" in the above anonymous block in our actual code base was done using "sql-model" clause, but modelled here as "rownum > 0" to represent as "non-mergeable" views

but when we run the same anonymous block in 21c, it works fine.
Questions:

a) so any workaround for this in 19c database ?
b) since we join based on the PK of these tables, the changes of getting the row duplication is not possible, so why the optimizer in 19c expects that it violates the "key-preserved" concepts, can you please explain on that?

demo@ATP21C> declare
  2     l_key number ;
  3  begin
  4     update ( select t1.x, t1.y, t2.new_y
  5             from t t1, ( select x, mod(y,3) as new_y
  6                             from t
  7                             where rownum > 0 ) t2
  8             where t1.x = t2.x )
  9     set y = new_y
 10     where y <> new_y
 11     returning max(x) into l_key ;
 12     dbms_output.put_line( ' key => '|| l_key );
 13  end;
 14  /
 key => 30

PL/SQL procedure successfully completed.

demo@ATP21C> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> create table t( x int primary key, y int );

Table created.

SQL>
SQL> insert into t(x,y)
  2      select 20+rownum, rownum
  3      from all_users
  4      where rownum <= 10;

10 rows created.

SQL>
SQL> create or replace
  2  view vvv as
  3  select t1.x, t1.y, t2.new_y
  4                 from t t1, ( select x, mod(y,3) as new_y
  5                                 from t
  6                                 where rownum > 0 ) t2
  7                 where t1.x = t2.x ;

View created.

SQL>
SQL>
SQL>
SQL> declare
  2         l_key sys.odcinumberlist := sys.odcinumberlist();
  3  type src is table of vvv%rowtype;
  4  s src;
  5      begin
  6      select *
  7      bulk collect into s
  8      from vvv;
  9
 10      forall i in 1 .. s.count
 11         update t
 12         set y = s(i).new_y
 13        where y <> s(i).new_y
 14        and x = s(i).x
 15       returning x bulk collect into l_key ;
 16
 17     end;
 18     /

PL/SQL procedure successfully completed.