Wrong results with multiple joins on Equal-Or-Both-Null on common columns

Detail name	Value
Changelog Number	12356
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 7.1.2, Exasol 7.0.14
Resolution Date	2021-10-20

Background

Queries containing "Equal or both null" joins are optimized if the syntax matches the example in EXASOL-2197.

Description

A query may return wrong results if the following conditions are met:

• The query contains at least 2 joins
• At least one join conditions uses the "Equal or both null" syntax
• Another join condition contains a column from the "Equal or both null" join condition

The NULL values resulting from the join are ignored and not returned. 

Preparation

drop schema if exists test cascade;
create schema test;

create table t1( i int );
create table t2( j int );
create table t3( k int );

insert into t1 values null, 1, 2, 3;
insert into t2 values null, 2, 3, 4;
insert into t3 values null, 3, 4, 5;

Example:

select * 
from t1 
join t2 on (t1.i=t2.j or (t1.i is null and t2.j is null))
join t3 on (t2.j=t3.k or (t2.j is null and t3.k is null));

Workaround

You can disable this optimization by setting the commandline-parameter -enableNullJoinOptimization=0.
Alternatively you can use COALESCE in the join condition to prevent the NULL-join only for that condition.

select * 
from t1 
join t2 on (t1.i=t2.j or (t1.i is null and t2.j is null))
join t3 on (t2.j=t3.k or (COALESCE(t2.j,NULL) is null and COALESCE(t3.k,NULL) is null));

These workarounds may negatively impact the query performance.

Fix

The query returns the correct results.