ClickHouse / ClickHouse/ClickHouse

Metamorphic equivalence violation in HAVING clauses due to buggy partition pruning on NaN values

Open
#106,533 3 comments 0 reactions 1 assignee Claimed by @alexey-milovidov View on GitHub
bug clickgap-analyzed comp-mergetree culprit-pr-pinned external
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

_No response_

### Describe what's wrong

## Summary

I discovered a **silent correctness bug** in ClickHouse where logically equivalent query formulations under De Morgan's laws return different results (cardinality mismatch: 1 vs 0).

The equivalence relation under test is:
$$\text{Query}(\text{NOT } (A \lor B)) \equiv \text{Query}(\text{NOT } A) \ \text{INTERSECT DISTINCT} \ \text{Query}(\text{NOT } B)$$

When the second query's independent filter $\text{NOT } B$ contains a function that produces `NaN` on negative values (such as `sqrt(negative)`), ClickHouse's partition pruner incorrectly evaluates `NOT(NaN > X)` as `false` and skips those partitions entirely. However, the first query's compound expression $\text{NOT}(A \lor B)$ prevents partition pruning from being applied, scanning the full table and evaluating the expression correctly on the CPU.

This execution path divergence leads to a direct silent correctness violation under our metamorphic testing framework.

### Does it reproduce on the most recent release?

Yes

### How to reproduce

`ClickHouse 26.3.12.3`

### 1. Setup Table and Data

```sql
DROP TABLE IF EXISTS t;
CREATE TABLE t (c0 Int32) ENGINE = MergeTree() ORDER BY c0 PARTITION BY c0;
INSERT INTO t VALUES (1), (-1);
```

### 2. Query 1: HAVING NOT (A OR B) — Returns 1 row (Correct)

*Here, $A \equiv \text{SUM}(c0) > 0$, and $B \equiv \text{sqrt}(c0) > 10$.*

```sql
SELECT c0 FROM t GROUP BY c0
HAVING NOT ((SUM(c0) > 0) OR (sqrt(c0) > 10))
SETTINGS aggregate_functions_null_for_empty=1, enable_optimize_predicate_expression=0;

-- Output:
-- ┌──c0─┐
-- │ -1 │
-- └─────┘
-- (1 row, correct. For c0 = -1, SUM(c0)>0 is false, sqrt(-1)>10 is NaN > 10 = false. false OR false = false. NOT(false) = true.)
```

### 3. Query 2: HAVING NOT A INTERSECT DISTINCT HAVING NOT B — Returns 0 rows (Incorrect!)

```sql
SELECT c0 FROM t GROUP BY c0
HAVING NOT (SUM(c0) > 0)
INTERSECT DISTINCT
SELECT c0 FROM t GROUP BY c0
HAVING NOT (sqrt(c0) > 10)
SETTINGS aggregate_functions_null_for_empty=1, enable_optimize_predicate_expression=0;

-- Output:
-- Empty set. (0 rows, incorrect! Mismatch with Query 1.)
```

---

### Expected behavior

- Any comparison involving `NaN` (such as `NaN > X`) should evaluate to `false` in IEEE 754 logic, meaning its negation `NOT (NaN > X)` must evaluate to `true` (represented as `1`).
- Partitions containing negative values should NOT be skipped during partition pruning when the filter is `NOT (sqrt(c0) > X)`.
- Query 1 and Query 2 should both return `c0 = -1`, satisfying the metamorphic equivalence relation.

### Error message and/or stacktrace

_No response_

### Related issues and pull requests

_No response_

### Additional context

_No response_

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.