[NOT] IN tuple predicate gives wrong results if the difference is after the value NULL on the LHS
Details
| Detail name | Value |
|---|---|
| Changelog Number | 32759 |
| Type | Bug |
| Status | Resolved |
| 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 |
| Fix Versions | Exasol 2025.2.0, Exasol 2025.1.17 |
| Resolution Date | 2025-12-17 |
Background
In SQL, two tuples (row value expressions) can only match as equal if they do not have any NULL values.
Two tuples that do contain NULLs compare as not equal if any pair of non-NULL values in the tuples does not match. For example (1, NULL, 2) and (1, 2, 3) do not match.
If all the non-NULL values match, for example (1, NULL, 3) and (1, 2, 3), then the result of the equality comparison is NULL. Note that a NULL BOOLEAN value is also called UNKNOWN.
Bug description
In Exasol, the IN predicate can incorrectly return NULL instead of false if the left-hand side contains NULL values and does not match any right-hand side row.
IN predicates
An IN predicate in a query can incorrectly return NULL instead of false, or a NOT IN predicate can incorrectly return NULL instead of true, if the following conditions apply:
- The right-hand side of the IN is a list of tuples.
- The number of elements in the left-hand side tuple is at least 2.
- The left-hand side tuple has at least one NULL value.
- The left-hand side tuple has at least one non-NULL value, and the corresponding values on the right-hand side are all non-NULL and different from the value on the left-hand side.
- The first element in the left-hand side tuple is NULL, or it matches the first element of at least one tuple from the right-hand side.
For example, see Q1 below.
The bug can also happen if the right-hand side is a subquery under these conditions, which are much less likely:
- The right-hand side of the IN is a subquery.
- The number of elements in the left-hand side tuple is at least 2.
- The left-hand side tuple has at least one NULL value.
- The left-hand side tuple has at least one non-NULL value, and the corresponding values on the right-hand side are all non-NULL and different from the value on the left-hand side.
- The first element in the left-hand side tuple is NULL, or it matches the first element of at least one tuple from the right-hand side.
- The subquery does not have an outer reference.
- The IN predicate is not in the WHERE or HAVING clause.
Predicates rewritten as tuple IN predicate
The optimizer recognizes AND predicates that are ORed together and can rewrite them to IN predicates which might be of the form above and can give wrong results. The conditions are:
- The query has an
ORpredicate in the SELECT list. - The
ORpredicate consists of at least 2ANDterms. - The
ANDterms are exactly 2 equality terms. - The equality terms each compare a column with a literal.
- Each
ANDterm uses the same two distinct columns. Note: Conditions 1 to 5 are the conditions for the second optimization described here. - One of the columns takes the value NULL.
- The other column is non-NULL and differs from the literal in every term
For example, see Q2 below.
Example
-- Setup create or replace table A (A1 int, A2 int); insert into A values (NULL, 0); -- Expected: 1 row, values (false) -- Observed: 1 row, values (NULL) select (A1, 0) in ((1, 2), (2, 3)) from A; -- Expected: 1 row, values (true) -- Observed: 1 row, values (NULL) select (A1, 0) not in ((1, 2), (2, 3)) from A; -- Q1 -- Expected: 1 row, values (NULL, 0) -- Observed: 0 rows select * from A where (A1, A2) not in ((1, 2), (3, 4)); -- Q2 -- Expected: 1 row, values (false) -- Observed: 1 row, values (NULL) select ((A1 = 1 and A2 = 2) or (A1 = 3 and A2 = 4)) from A;
Workaround
IN predicates
Expand the IN predicate as separate equality predicates. Wrap one equality predicate for each tuple in COALESCE(..., NULL) to ensure that the optimizer does not convert the expression back to an IN predicate (this is only necessary for tuples of length 2).
For example, Q1 above is equivalent to
-- Expected/Observed: 1 row, values (NULL, 0) select * from A where not ((coalesce(A1 = 1, NULL) and A2 = 2) or (coalesce(A1 = 3, NULL) and A2 = 4));
Predicates rewritten as tuple IN predicates
As above, wrap one equality predicate in COALESCE(..., NULL) to ensure that the optimizer does not convert the expression to an IN predicate.
-- Expected/Observed: 1 row, values (false) select ((coalesce(A1 = 1, NULL) and A2 = 2) or (coalesce(A1 = 3, NULL) and A2 = 4)) from A;
Fix
The query returns the correct result.