Null join optimization leads to wrong results for outer joins

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

Description

Exasol uses a special optimization for inner joins with join conditions such as

--null join
select * from t1 inner join t2 on
t1.id=t2.id or (t1.id is null and t2.id is null)

This optimization is also erroneously applied to outer joins, which leads to incorrect results.

Workaround

You can disable this optimization via a parameter:

-enableNullJoinOptimization=0

Alternatively, you can use coalesce in the join condition to prevent the optimization:

on t1.id=t2.id or 
(coalesce(t1.id, null) is null and coalesce(t2.id, null) is null);