Fixed bug in View Materialization Optimization concerning with Nested Views declared using WITH clause

Detail name	Value
Changelog Number	4391
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0
Fix Versions	EXASOL 6.0.3
Resolution Date	2018-01-09

Bug

Sometimes, when a single view is used multiple times within a query, an optimization to materialize (compute the result of) such views is applied. An algorithm decides which views to be materialized and which views to be eliminated (replaced by their query terms). There was a bug in this algorithm, resulting in an Internal Server Error for affected statements.

Views can be defined in two ways,

• using the CREATE VIEW clause : These views belong to the namespace of the schema, and can be used globally.
• using the WITH clause : These views are temporary, they override views of the same name in the current namespace, and can be used only in the SELECT query that is part of the WITH clause.

The problem occurs when a view defined using WITH clause is used inside another view defined using CREATE VIEW clause, for example

CREATE VIEW v1 as 
    with v2 as
        (select ...)
    (select ...);

When v1 is used multiple times in the query, this error may occur.

Reproducibility
A simplified setup to reproduce the error:

create schema test;

create or replace table t1 (d decimal);
create or replace table t2 (d decimal);

CREATE OR REPLACE VIEW v3 as
    WITH v4 as
        (select d from t2)
    select t2.d from t2 left join v4 on t2.d = v4.d;

WITH 
    v2 as 
        (select d from t1), 
    v1 as 
        (select v2.d from v2 left join v3 v31 on v2.d = v31.d left join v3 v32 on v2.d = v32.d) 
    select d from v1;

View v4 is defined using WITH clause inside view v3 which is defined using CREATE VIEW clause.

Workaround
The workaround is to avoid using the WITH clause inside CREATE VIEW clause. Definition of View v3 can be done as follows.

CREATE OR REPLACE VIEW v4 as
        (select d from t2);
CREATE OR REPLACE VIEW v3 as
    select t2.d from t2 left join v4 on t2.d = v4.d;