ClickHouse / ClickHouse/ClickHouse
arrayIntersect emits a NULL present only in the first array, so the result depends on the argument order
- 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
Assessment
This issue has not been assessed yet.