[NOT] EXISTS expression in combination with OUTER JOIN can lead to wrong results

Detail name	Value
Changelog Number	32632
Type	Bug
Status	Open
Affected Versions	Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0

Description

[NOT] EXISTS (…) expression can be incorrectly evaluated to NULL instead of FALSE/TRUE when following conditions apply:

• Subselect after EXISTS is correlated.
• This outer column lookup references a table from NULL-generating side of an outer join (e.g. right side of a LEFT JOIN).

Example

create table t_outer(x int);
insert into t_outer values 1, 2;
create table t_null(y int);
insert into t_null values 1, 3;

-- Expected 1 row: | 2 | NULL |
-- Observed 0 rows
select x, y from t_outer left join t_null on x=y where not exists (select x from t_outer where x=y);

Workaround

Add to the predicate IS TRUE or IS FALSE. For example, for a query from above:

select x, y from t_outer left join t_null on x=y where exists (select x from t_outer where x=y) is false;

Fix

Query runs as expected.