Internal server error in certain outer joins on subselects

Details

Detail name Value
Changelog Number 5906
Type Bug
Status Resolved
Affected Versions EXASOL 6.0.0, EXASolution 5.0.0
Fix Versions EXASolution 5.0.22, EXASOL 6.0.9
Resolution Date 2018-04-10
Description

An internal server error can occur in some cases where outer joins are executed on subselects. The following conditions are required to trigger the problem:

  1. The query contains an outer join
  2. The inner table of the outer join is a subselect with the following conditions (i.e. the right table of a left outer join or the left table of a right outer join):
  3. The outer join from (2) itself is contained in yet another subselect
  4. On the subselect (3) there is a filter that contains an expression that references only the column from 2.1 (mandatory) and columns from the subselect 2.3 and constants (optional). Note: This means the outer join from 2 itself can be replaced by an inner join on a semantic layer.
  1. Its select list contains a column that is either constant or has an expression that potentially converts NULL values to non-NULL-values (e.g. coalesce, CASE, etc)
  2. The structure is relatively simple: No GROUP BY, no analytic functions, scalar emits functions, correlations, etc.
  3. It contains another subselect - in case of no constant in 2.1 all column references in the expression are from this subselect

Some of these conditions, especially the subselects, might not be explicitly part of the SQL text. This is because the internal optimizer rewrites some parts of a query into subselects. This happens e.g. for nested outer joins.

Views also count as subselects in this description. However, starting from EXASOL 6.0 it is much less likely that this problem happens with views.

Example
-- outer_select is the subselect contained in the outer join
-- union_view is the subselect contained in outer_select
-- 'x' is the constant column in os (not using an expression in this case)
-- subsel is the subselect containing the outer join
-- x='y' is the condition on subsel referencing only the constant column

with union_view as (select j,i from t1 union all select j,i from t2)
select * from (
    select fact_table.i, subsel.x 
    from fact_table left join (select j, i, 'x' as x from union_view) as outer_select on fact_table.i = outer_select.i
    ) subsel
where x = 'y'
;
Workaround
  • Replace the outer join from 1 by an inner join
  • Put the filter from 4 inside the subselect 3
  • Add additional (no-impact) columns from other tables to the filter from 4 (e.g. something like x||i = 'y'||i in the example above)