Expression with TYPEOF in combination with OUTER JOIN can lead to wrong results

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

TYPEOF function may be incorrectly evaluated to NULL instead of a string when its argument is a column lookup referencing 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 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 typeof(y) like 'DECIMAL%';

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 (typeof(y) like 'DECIMAL%') is true;

Fix

Query runs as expected.