BETWEEN expression in combination with OUTER JOIN can lead to wrong results

Detail name	Value
Changelog Number	32495
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] BETWEEN expression can be incorrectly evaluated to NULL instead of FALSE/TRUE when following conditions apply:

• Lower or upper bound 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).
• Searched expression is not between lower and upper bounds.

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 not between 1 and y;

Workaround

Rewrite BETWEEN expression using simple comparison predicates. For an example above:

select x, y from t_outer left join t_null on x=y
where 0 < 1 or 0 > y;

Fix

Query runs as expected.