TUPLE [NOT] IN (…) expression in combination with OUTER JOIN can lead to wrong results

Detail name	Value
Changelog Number	32631
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

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

• One of values in the searched expression is a column lookup.
• This column lookup references a table from NULL-generating side of an outer join (e.g. right side of a LEFT JOIN).
• One of values in the searched expression does not match with any corresponding values from tuples on the right side.

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 2 rows: | 1 | 1 | and | 2 | NULL |
-- Observed 1 row: | 1 | 1 |
select x, y from t_outer left join t_null on x=y where (0, y) not in (1,2);

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 ((0, y) in (1,2)) is false;

Fix

Query runs as expected.