[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:

  1. The right-hand side of the IN is a list of tuples.
  2. The number of elements in the left-hand side tuple is at least 2.
  3. The left-hand side tuple has at least one NULL value.
  4. 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.
  5. 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:

  1. The right-hand side of the IN is a subquery.
  2. The number of elements in the left-hand side tuple is at least 2.
  3. The left-hand side tuple has at least one NULL value.
  4. 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.
  5. 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.
  6. The subquery does not have an outer reference.
  7. 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:

  1. The query has an OR predicate in the SELECT list.
  2. The OR predicate consists of at least 2 AND terms.
  3. The AND terms are exactly 2 equality terms.
  4. The equality terms each compare a column with a literal.
  5. Each AND term uses the same two distinct columns. Note: Conditions 1 to 5 are the conditions for the second optimization described here.
  6. One of the columns takes the value NULL.
  7. 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.