Filter is not propagated to outer join

Details

Detail name Value
Changelog Number 10696
Type Bug
Status Resolved
Affected Versions Exasol 6.2.0, Exasol 7.0.0
Fix Versions Exasol 7.0.1, Exasol 6.2.11
Resolution Date 2020-09-23

Description

In certain situations, filters are not propagated to outer joins properly which can lead to degraded performance. Preconditions for this case are:

  • an outer join followed by an inner join
  • an equal filter that is using columns from both tables of the outer join on one side

Example:

SELECT base.my_key
FROM base LEFT JOIN override ON override.my_key = base.my_key
JOIN some_data ON data_key = base.my_key 
WHERE nvl(override.other_name, base.default_name) = 'X';

Workaround

You can restore the desired behavior by setting the following parameter:

disableJoinConditionMoving=1

Fix

The filter is propagated to the outer join to improve performance.