lineage_util does not traverse chains of synonyms

[rvo-cs]

In keeping with issue #63, chains of synonyms are not traversed by the lineage_util package.

Reason: dd_util.resolve_synonym performs only a "shallow" resolution, as opposed to "in-depth" resolution of chains of synonyms, up to the first non-synonym object (if any) at the end of the chain.

Test case:

alter session set current_schema = "SCOTT";

create table tc (
   c1 number,
   c2 number,
   c3 number,
   c4 number,
   c5 number,
   c6 number,
   c7 number,
   c8 number
);

create or replace public synonym s_tc for tc;
create or replace synonym s_tc for "PUBLIC".s_tc;
create or replace public synonym s2_tc for s_tc;
drop synonym s_tc; -- this invalidates "PUBLIC".s2_tc

create or replace view vd (d1, d2, d3, d4)
as
   select c1 + c2,
          c4 + c3,
          c5 + c6,
          c7 + c8
     from s2_tc
;
-- "PUBLIC".s2_tc is revalidated
-- The chain of synonyms is:
--    "PUBLIC".s2_tc -> "PUBLIC".s_tc (scott.s_tc is NON-EXISTENT) -> scott.tc

create or replace public synonym s_vd for vd;
create or replace synonym s_vd for "PUBLIC".s_vd;
create or replace public synonym s2_vd for s_vd;
drop synonym s_vd; -- this invalidates "PUBLIC".s2_vd

create or replace view ve (e1, e2)
as
   select d1 + d2,
          d3 + d4
     from s2_vd
;
-- "PUBLIC".s2_vd is revalidated
-- The chain of synonyms is:
--    "PUBLIC".s2_vd -> "PUBLIC".s_vd (scott.s_vd is NON-EXISTENT) -> scott.vd

select *
  from table(
          lineage_util.get_dep_cols_from_view(
             in_owner       => 'SCOTT',
             in_object_name => 'VE',
             in_column_name => 'E1',
             in_recursive   => 1
          )
       );

Actual:

OWNER	OBJECT_TYPE	OBJECT_NAME	COLUMN_NAME
(null)	(null)	(null)	D1
(null)	(null)	(null)	D2

Expected:

OWNER	OBJECT_TYPE	OBJECT_NAME	COLUMN_NAME
SCOTT	TABLE	TC	C1
SCOTT	TABLE	TC	C2
SCOTT	TABLE	TC	C3
SCOTT	TABLE	TC	C4
SCOTT	VIEW	VD	D1
SCOTT	VIEW	VD	D2

This is arguably a made-up test case, for an edge case; yet it's perfectly legal, so it could be found.

Solution: for completeness, dd_util.resolve_synonym should be expanded in order to perform in-depth resolution of chains of synonyms—perhaps with an option to perform shallow resolution if needed, but in-depth resolution should be the default.

[PhilippSalvisberg]

Yes, this is indeed one of many limitations of the current implementation.

I agree it's allowed to create a private synonym for a public synonym, but it is strange and IMO a corner case.

In your example you created the following synonym chains:

• public.s2_tc -> scott.s_tc -> public.s_tc -> scott.tc
• public.s2_vd -> scott.s_vd -> public.s_vd -> scott.vd

I did not drop the synonyms and the dba_dependencies looked like this:

OWNER  NAME  TYPE    REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE     REFERENCED_LINK_NAME DEPENDENCY_TYPE
------ ----- ------- ---------------- --------------- ------------------- -------------------- ---------------
PUBLIC S_TC  SYNONYM SCOTT            TC              TABLE                                    HARD           
PUBLIC S2_TC SYNONYM SCOTT            S_TC            SYNONYM                                  HARD           
PUBLIC S_VD  SYNONYM SCOTT            VD              VIEW                                     HARD           
SCOTT  S_TC  SYNONYM PUBLIC           S_TC            SYNONYM                                  HARD           
SCOTT  VD    VIEW    PUBLIC           S2_TC           SYNONYM                                  HARD           
SCOTT  S_VD  SYNONYM PUBLIC           S_VD            SYNONYM                                  HARD           
PUBLIC S2_VD SYNONYM SCOTT            S_VD            SYNONYM                                  HARD           
SCOTT  VE    VIEW    PUBLIC           S2_VD           SYNONYM                                  HARD           

8 rows selected. 

After dropping the private synonyms s_tc and s_vd the dba_dependencies look like this:

OWNER  NAME  TYPE    REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE     REFERENCED_LINK_NAME DEPENDENCY_TYPE
------ ----- ------- ---------------- --------------- ------------------- -------------------- ---------------
PUBLIC S_TC  SYNONYM SCOTT            TC              TABLE                                    HARD           
PUBLIC S2_TC SYNONYM SCOTT            S_TC            NON-EXISTENT                             HARD           
PUBLIC S_VD  SYNONYM SCOTT            VD              VIEW                                     HARD           
SCOTT  VD    VIEW    PUBLIC           S2_TC           SYNONYM                                  HARD           
PUBLIC S2_VD SYNONYM SCOTT            S_VD            NON-EXISTENT                             HARD           
SCOTT  VE    VIEW    PUBLIC           S2_VD           SYNONYM                                  HARD           

6 rows selected. 

The dependency chain changed to:

• public.s2_tc -> public.s_tc -> scott.tc
• public.s2_vd -> public.s_vd -> scott.vd

That we have to deal with a referenced_type NON-EXISTENT is just another corner case.

Nonetheless I agree that the result of this case could be improved. - I consider this an enhancement request.

[rvo-cs]

The public synonym has to depend on the NON-EXISTENT object, so it's invalidated if an object with the referenced name is created again in the schema of the referenced owner.

And I totally agree, chains of synonyms are infrequent and mostly corner cases.