Views are computed without filters if used at least twice and filters are only in ON-clause of Outer Joins

Detail name	Value
Changelog Number	7011
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, EXASOL 6.0.7
Fix Versions	Exasol 6.2.0, Exasol 6.1.3
Resolution Date	2019-05-07

Description

If a view is used twice or more often in a query, an internal optimization decides whether it would be faster to compute the view only and use the computed view several times. This should happen only if each use of the query is without filters since filters often reduce the computation time of a query a lot.

However, due to a bug fix in the way we pushdown filters into subqueries (see EXASOL-2190) the detection of filters on views is broken for views joined with outer joins.

Example

drop schema if exists test cascade;
create schema test;

create table t1( i int, j int);
create table t2( k int, l int);
create view v as (select k as m, l as n from t2 group by k,l);

-- filter in on-clause of left join is not detected
select * from
t1 
join v good_filter on good_filter.m=t1.i and good_filter.n=7
left join v not_detected_filter on not_detected_filter.m=t1.i and not_detected_filter.n=8
;

Workaround

As this problem is deep within the optimizer it is not easy to work around it. One possibility is to manually put the view with filter into a subquery and add ORDER BY FALSE to force materialization with the filter:

select * from
t1 
join v good_filter on good_filter.m=t1.i and good_filter.n=7
left join (select * from v where n=8 order by false) manual_filter on manual_filter.m=t1.i

Another possible alternative would be to disable the view materialization optimization if the problem causes severe problems on the system. Since the optimization should usually reduce materialization times this might also decrease general performance on systems with heavy view modelling.