Invalid outer join to inner join optimization

Details

Detail name Value
Changelog Number 14895
Type Bug
Status Resolved
Affected Versions Exasol 7.1.0
Fix Versions Exasol 8.0.3, Exasol 7.1.12
Resolution Date 2022-06-10

Background

In some cases, the optimizer can transform an outer join into an inner join.

Description

The outer join to inner join optimization can be incorrectly applied, leading to incorrect results, if the following conditions are met:

  • The query contains a left join
  • The filter that potentially allows transforming the left joins into an inner join is a CASE expression

Example:

select 
    c1, id1, t2_left.id, id2, t2_right.id, t2_left.text, t2_right.text, 
    case
        when c1 in (100) then t2_left.text
        when c1 in (200) then t2_right.text
    end as case_filter
from t1 
    left join t2 t2_left  on t2_left.id=t1.id1
    left join t2 t2_right on t2_right.id=t1.id2
where 
    local.case_filter = 'ttt'

 

Workaround

Deactivate this optimization by setting the database parameter -outerJoinRemovalLevel=0

Fix

The query returns correct results by keeping outer joins in those scenarios.