Missing view dependencies in EXA_DBA_DEPENDENCIES

Detail name	Value
Changelog Number	14914
Type	Bug
Status	Open
Affected Versions	Exasol 7.1.0, Exasol 8.0.0

Background

A view can be valid/invalid.
For invalid views, EXA_DBA_DEPENDENCIES shows NULL in column REFERENCED.
The first read access automatically tries to validate an invalid view.
For more details see the documentation.

Description

For valid views, EXA_DBA_DEPENDENCIES may not show the dependencies but NULL in column REFERENCED, if the following conditions are met:

• A previously invalid view gets validated again
• The view depends on multiple objects
• The view contains a subselect
• The subselect contains an IMPORT

Create or replace view is not affected.

Preparation

create or replace table test.t1 as select 0 col0 from dual;

create or replace view test.v1 as select col0 from test.t1 union all select * from (import from exa at 'localhost;encryption=0' user 'sys' identified by '...' statement 'select 0');

Example

-- Fist invalidate the view
alter table test.t1 add column (col1 integer);

-- As expected: EXA_DBA_DEPENDENCIES shows NULL in column REFERENCED
select * from sys.exa_dba_dependencies d where d.object_schema = 'TEST' and d.object_name = 'V1';

-- Now validate the view again
select * from test.v1;

-- Unexpected: EXA_DBA_DEPENDENCIES still shows NULL in column REFERENCED
select * from sys.exa_dba_dependencies d where d.object_schema = 'TEST' and d.object_name = 'V1';

Workaround

Recreate invalid views via "create or replace view".

Fix

EXA_DBA_DEPENDENCIES contains all dependencies