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
INorNOT INevaluates toNULL. - The IN list contains only
NULLvalues 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.