ClickHouse / ClickHouse/ClickHouse

arrayIntersect emits a NULL present only in the first array, so the result depends on the argument order

Open
#117,608 1 comment 0 reactions 1 assignee Claimed by @antaljanosbenjamin View on GitHub
bug clickgap-analyzed comp-regular-function culprit-pr-not-found
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

**Describe what's wrong**

`arrayIntersect` decides whether to emit `NULL` from the first argument alone. When every argument type is Nullable and the first array contains a `NULL` element, that `NULL` appears in the result even if no other array contains `NULL` — so the intersection contains an element that is not present in all of the arrays, and swapping the arguments of this commutative function changes the result.

The mechanism is in the `Intersect` branch of `FunctionArrayIntersect::execute` (`src/Functions/array/arrayIntersect.cpp` on current master): the per-argument pass correctly clears `all_has_nullable` when some argument has no `NULL` in the current row (line 760), but the emission loop over the first array's elements then resets it to the blanket all-arguments-are-Nullable flag (`all_has_nullable = all_nullable;`, line 812), discarding that computation, and emits the `NULL` at line 818 based on the reset flag. Both the typed numeric branch and the generic branch are affected, as is the multi-argument form.

This is long-standing, not a recent regression: it reproduces on current master / 26.9 and at least as far back as 23.8.

**How to reproduce**

Pure defaults, any recent version:

```sql
-- NULL is present only in the first array, absent from the second, yet it is in the "intersection":
SELECT arrayIntersect(CAST([NULL, 1] AS Array(Nullable(UInt8))), CAST([1, 2] AS Array(Nullable(UInt8)))) AS r;
-- [NULL,1] (wrong)

-- The same intersection with the arguments swapped:
SELECT arrayIntersect(CAST([1, 2] AS Array(Nullable(UInt8))), CAST([NULL, 1] AS Array(Nullable(UInt8)))) AS r;
-- [1] (correct)

-- Control: NULL genuinely present in both arrays:
SELECT arrayIntersect(CAST([NULL, 1] AS Array(Nullable(UInt8))), CAST([NULL, 2] AS Array(Nullable(UInt8)))) AS r;
-- [NULL] (correct)

-- Three arguments, NULL only in the first:
SELECT arrayIntersect(CAST([NULL, 1] AS Array(Nullable(UInt8))), CAST([1, 2] AS Array(Nullable(UInt8))), CAST([1, 3] AS Array(Nullable(UInt8)))) AS r;
-- [NULL,1] (wrong)

-- The generic (non-numeric) branch has the same defect:
SELECT arrayIntersect(CAST(['a', NULL] AS Array(Nullable(String))), CAST(['a', 'b'] AS Array(Nullable(String)))) AS r;
-- ['a',NULL] (wrong; swapped order returns ['a'])
```

Commutativity check over a table:

```sql
CREATE TABLE t (id UInt32, a Array(Nullable(UInt8)), b Array(Nullable(UInt8))) ENGINE = Memory;
INSERT INTO t VALUES (1, [NULL, 1], [1, 2]), (2, [1, 2], [NULL, 1]), (3, [NULL, 1], [NULL, 2]);
SELECT id, arrayIntersect(a, b) AS ab, arrayIntersect(b, a) AS ba, ab = ba AS commutative FROM t ORDER BY id;
```

```
1 [NULL,1] [1] 0
2 [1] [NULL,1] 0
3 [NULL] [NULL] 1
```

**Expected behavior**

The result of `arrayIntersect` contains `NULL` only when every argument array contains `NULL`. `arrayIntersect(CAST([NULL, 1] AS Array(Nullable(UInt8))), CAST([1, 2] AS Array(Nullable(UInt8))))` returns `[1]`, and `arrayIntersect(a, b) = arrayIntersect(b, a)` holds for all inputs.

**Additional context**

This is distinct from #114587 (the overflow guard on the same function, which produces wrong matches of out-of-range values) and from the declared-vs-produced result-nullability mismatch fixed by #117577: the blanket reset survives that fix, since for the all-Nullable-arguments case the old `all_nullable` flag and the new declared-type flag are both true. `arrayUnion` and `arraySymmetricDifference` are not affected (a `NULL` in any argument legitimately belongs to their results).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.