Internal Server Error when using Aggregation Functions in Analytic Function or UNION ALL together with Join of a View

Details

Detail name Value
Changelog Number 5257
Type Bug
Status Resolved
Affected Versions EXASOL 6.0.4
Fix Versions EXASOL 6.0.6
Resolution Date 2017-10-27
Bug

An 'internal server error' error message can occur in some situations where an aggregation function is used in combination with a join and several views.

The following conditions need to be met in detail for the bug to appear

  • an aggregation function is used
  • the FROM-clause of the SELECT containing the aggregation function
  1. is a join
  2. contains a view
  3. contains another view at least twice (directly or through other views - even the view from 2)

AND either of the following restrictions on the position of the aggregation function is met

  1. the aggregation function is used either as argument for an analytic function that is used in an expression
  2. OR the aggregation function is used in the select list of a UNION ALL that has another SELECT-operand with NULL in select list
Example
create or replace table t( i int);
create view bug_v as (select * from t);
create view mat_v as (select * from t);

-- 1. expression with analytic function
select count(*) + (SUM(COUNT(*)) OVER())
from bug_v cross join (select * from mat_v,mat_v);

-- 2. union all with NULL
select null from dual
union all
select count(*)
from bug_v cross join (select * from mat_v,mat_v);
Workaround

The workarounds for both situations are different.

  1. For expression with analytic function the expression needs to be removed from that select and calculated in a select around it:
  2. For UNION ALL with NULL the NULL needs to be casted to the needed type: 
select expr1 + expr2
from
( select
    count(*) expr1,
    (SUM(COUNT(*)) OVER()) expr2
  from bug_v cross join (select * from mat_v,mat_v)
);
select cast(null as int) from dual
union all
select count(*)
from bug_v cross join (select * from mat_v,mat_v);