internal server error for query with views and UNION

Details

Detail name Value
Changelog Number 4835
Type Bug
Status Resolved
Affected Versions
Fix Versions EXASOL 6.0.3
Resolution Date 2017-08-15
Short description

Some situations might lead to an internal server error due to a bug in our algorithm to decide when a view should be materialized (i.e the whole view computed into a temporary table which is reused each time the view is used in the query) and when it should be replaced by its query text wherever it is used.

Details

Here are the minimal requirements to reproduce this problem:

  • The query contains UNION ALL - this triggers a special code path in optimization
  • AND one operand of the UNION ALL has a select list column with an expression that references both of
  1. a column of a select that contains a view V_MULTIPLE_USED twice
  2. AND a column of another view V_ONLY_REFERENCED

In that case we get an internal server error.

Testcase:

drop schema test cascade;
create schema test;

CREATE TABLE t (c1 int);

WITH referenced_view AS (SELECT c1 FROM t),
other_view as (select c1 from t)
SELECT subsel.c1 + rv.c1 AS res
FROM 
  (select ov1.c1 from other_view ov1
             JOIN other_view ov2 ON ov2.c1 = ov1.c1
  ) subsel
  JOIN referenced_view rv
		ON rv.c1 = subsel.c1
UNION ALL
	SELECT NULL uc1
;

The situation might not always be as straight-forward as it looks in this testcase:

  • The subselect (1) might not be in the query text but instead might be the result of query rewrites by the SQL compiler in case of OUTER JOINs.
  • Further, there might be more situations than just UNION ALL that lead to the special code path that causes this problem. E.g. Analytic functions could trigger this problem in certain situations.
Workaround

Due to the complexity of the situation it is usually not possible to work around this situation in the SQL.

The only reliable way to avoid this problem is to disable our new view materialization optimization until this bug is fixed (parameter -disableViewOptimization=true in EXAOperation). However, this might affect system performance.