Wrong results in outer join with simple case in join condition

Detail name	Value
Changelog Number	11757
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.7, Exasol 6.2.14
Fix Versions	Exasol 7.0.9, Exasol 6.2.15
Resolution Date	2021-03-30

Description

A Query returns wrong results if the following conditions are met:

• The query contains a simple CASE (CASE <expression> WHEN <comparison> THEN ...)
• The CASE statement returns varchar
• The CASE is used in an outer join

Preparation

create table a(x int, y int);
create table b(x int, z varchar(100));

insert into a values (1,1),(2,2);
insert into b values (1,'10'),(2,'20');

Example

select * 
from a 
left join b on b.z = case a.y when 2 then '20' end;

Workaround

Use the searched CASE (CASE WHEN <condition> THEN ...) instead:

select * 
from a 
left join b on b.z = case when a.y = 2 then '20' end;

Fix

The query returns the correct results