Incorrect results for IN and NOT IN with all-NULL lists

Details

Detail name Value
Changelog Number 33178
Type Bug
Status Resolved
Affected Versions Exasol 2026.1.0, Exasol 2025.1.11
Fix Versions Exasol 2025.1.17
Resolution Date 2026-11-12

Description

Queries using IN or NOT IN return incorrect results when the following conditions apply:

  • The column or expression before IN or NOT IN evaluates to NULL.
  • The IN list contains only NULL values and has at least two entries, such as (NULL, NULL).

The condition should return NULL. Instead, IN returns FALSE and NOT IN returns TRUE. This can cause a NOT IN filter to include rows that should be excluded.

Example

-- Observed: 2 rows containing 1 and NULL.
-- Expected: 1 row containing 1.
SELECT v
FROM VALUES (1), (NULL) AS t(v)
WHERE v NOT IN (NULL, NULL);

Workaround

For a NOT IN filter, add an IS NOT NULL condition for the tested value to the WHERE clause.

-- Observed/Expected: 1 row containing 1.
SELECT v
FROM VALUES (1), (NULL) AS t(v)
WHERE v IS NOT NULL
  AND v NOT IN (NULL, NULL);

Fix

The query returns the correct result.