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

[Manuel-Neuer1]

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

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$.

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!)

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

[PedroTadim]

Issue with partition pruning and nans since v26.3:

Partition
  Condition: (sqrt(c0) in (-Inf, 10.])
  Parts: 1/2
The partition pruning transforms NOT (sqrt(c0) > 10) into sqrt(c0) <= 10. These are NOT equivalent when
NaN is involved:

NOT (NaN > 10) = NOT false = true (row should be kept)
NaN <= 10 = false (row is pruned)
The partition for c0=-1 (where sqrt(-1) = NaN) is incorrectly pruned because the range (-Inf, 10.] doesn't
include NaN.

@groeneai can you fix this?

[groeneai]

Filed #106568. Root cause: KeyCondition::cloneDAGWithInversionPushDown rewrites not(comparison) via the inverse_relations table, which produces an algebraically-invalid identity under IEEE-754 NaN: not(NaN > c) = true but NaN <= c = false. Partition pruning then evaluated the wrong condition and dropped the c0 = -1 partition. The fix skips the inverse rewrite for the six comparison ops when an operand can be NaN; constant floats are checked statically, so Int <= 5.5 still gets the full inversion.

[clickgapai]

Found via ClickGap automated review. Please close or comment if this is incorrect or needs adjustment.

TL;DR

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

ClickGap verified: reproduced it with the script below on master; tested 5 version(s) — 4 affected, 1 clean (see Affects).

Reproduction

#!/bin/bash
# Issue #106533 — Metamorphic equivalence violation in HAVING clauses due to
# buggy partition pruning on NaN values.
#
# Faithful to the reporter's exact repro: a MergeTree table PARTITION BY c0
# with rows (1), (-1). Two logically-equivalent (De Morgan) formulations:
#   Q1: HAVING NOT ((SUM(c0)>0) OR (sqrt(c0)>10))          -- compound, no pruning
#   Q2: HAVING NOT (SUM(c0)>0) INTERSECT DISTINCT NOT (sqrt(c0)>10)  -- independent filter
# Q1 (control) scans the full table and correctly returns c0=-1. Q2's
# independent NOT(sqrt(c0)>10) filter lets the partition pruner mis-evaluate
# NaN (sqrt(-1)) and skip the c0=-1 partition, returning the empty set.
#
# Self-validating ($1 = clickhouse binary):
#   exit 1   -> bug reproduces (Q1={-1}, Q2 diverges: metamorphic violation)
#   exit 0   -> clean: queries RAN and Q1==Q2 (no divergence)
#   exit 125 -> inconclusive: a query ERRORED / feature/setting absent
#   exit 124 -> timed out (flaky)
#
# Honors the affects-matrix plan sweep: every `local` invocation appends the
# swept setting via CLICKGAP_REPRO_SETTINGS (a NaN/partition-pruning/optimizer
# bug can be masked under one plan and visible under another).
set -uo pipefail
BIN="$1"
SWEEP=${CLICKGAP_REPRO_SETTINGS:+--$CLICKGAP_REPRO_SETTINGS}
DATADIR=$(mktemp -d)
trap 'rm -rf "$DATADIR"' EXIT
COMMON="SETTINGS aggregate_functions_null_for_empty=1, enable_optimize_predicate_expression=0"

is_err() { echo "$1" | grep -qiE "exception|Code: [0-9]+|NOT_IMPLEMENTED|UNKNOWN_[A-Z_]+"; }

setup=$(timeout 60 "$BIN" local --path "$DATADIR" $SWEEP --multiquery --query "
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>&1)
setup_rc=$?
[ "$setup_rc" -eq 124 ] && { echo "setup timed out"; exit 124; }
is_err "$setup" && { echo "setup errored -> inconclusive: $setup"; exit 125; }

q1=$(timeout 30 "$BIN" local --path "$DATADIR" $SWEEP --query \
  "SELECT c0 FROM t GROUP BY c0 HAVING NOT ((SUM(c0) > 0) OR (sqrt(c0) > 10)) $COMMON" 2>&1)
rc1=$?
q2=$(timeout 30 "$BIN" local --path "$DATADIR" $SWEEP --query \
  "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) $COMMON" 2>&1)
rc2=$?
{ [ "$rc1" -eq 124 ] || [ "$rc2" -eq 124 ]; } && { echo "query timed out"; exit 124; }
{ is_err "$q1" || is_err "$q2"; } && { echo "query errored -> inconclusive: [$q1] [$q2]"; exit 125; }

echo "Q1  NOT(A OR B)                 => [$(echo $q1)]"
echo "Q2  NOT A INTERSECT NOT B       => [$(echo $q2)]"

norm() { printf '%s' "$1" | tr -d ' \t\r' | grep -v '^$' | sort; }
n1=$(norm "$q1"); n2=$(norm "$q2")

if [ "$n1" = "-1" ] && [ "$n1" != "$n2" ]; then
  echo "METAMORPHIC VIOLATION: Q1={$n1} Q2={$n2} -> bug reproduces"
  exit 1
fi
echo "Q1={$n1} Q2={$n2} (no attributable divergence) -> clean"
exit 0

Bisect

• Bisected on master in 9 steps (confidence: MEDIUM); a single introducing PR could not be pinned (untested merges with no prebuilt binary in the boundary range).
• Candidate range: d47bed69c9c1..eea8ff0cab02
• A source-build bisect over the range will be attempted to pin the exact commit.

Affects

• Tested on master @ commit 38129a8480db (aarch64)
• Reproduces on: master, 26.5, 26.4, 26.3
• Does NOT reproduce on: 25.8
• Note: affects matrix shows a boundary, but bisect outcome INCOMPLETE_RANGE did not verify an introducing release — a backport recommendation requires maintainer confirmation.

Suggested next step

Manual triage is needed. The reproducer above should be a fast first check.

Severity: P2

---

ClickGap · Finding: h_issue_106533