A fixed epsilon cannot tell when a SQL aggregate is wrong

Equality is not an oracle for floating point

Benchmarking a SQL system often ends with a deceptively simple question: did two executions return the same answer? For floating-point aggregates, exact equality can reject two legitimate answers because addition is not associative, while a fixed tolerance can hide an error that rounding cannot explain. A new condition-aware oracle replaces both shortcuts with exact rational ground truth for the stored binary64 values and an error bound tied to the aggregate algorithm. It classifies each answer as exact, bounded, indeterminate, or anomalous rather than treating every cross-engine disagreement as a bug. The paper defines the four-way rule and its arithmetic basis.

The algorithm changes what can be tested

The key result is that the testability boundary belongs to the implementation, not only to the SQL function. For SUM and AVG, relative error grows linearly with the condition number. Variance computed with the textbook one-pass formula grows quadratically, while Welford or two-pass variance grows linearly. At 10,000 rows, the paper places the one-pass variance boundary near κ=9.5×10^5, versus roughly κ=9.0×10^11 for the linear case—a decidable window about one million times narrower. Beyond the boundary, the bound admits any discrepancy, so the oracle correctly says that the result is indeterminate rather than correct or buggy. Those boundaries and assumptions are derived in Sections 2 and 3.

Measurements across PostgreSQL 17, MySQL 8.4, ClickHouse 25.3, DuckDB 1.5.4 and SQLite 3.45.1 recover that split. ClickHouse’s varPop has a measured error slope of 2.05 and becomes indeterminate around κ=10^6; the other measured implementations have slopes no higher than 1.05 and remain bounded through κ=10^9. Across 70 variance cells and 70 sum cells, the authors report zero anomalies. That negative result matters: the exercise validates the oracle’s bound; it does not claim every engine is bug-free. The evaluation reports the engine versions, measured slopes and zero-anomaly result.

Ordinary representations can cross the line

This is not only a fuzzer pathology. The paper reports that ordinary storage conventions—including epoch-nanosecond timestamps and tightly clustered sensor values—can push variance into the unstable regime. On tested cases, ClickHouse’s one-pass variance was wrong by as much as 2,100%, while stable implementations stayed accurate to eleven digits. The authors also report zero standard deviation, NaN correlation and wrong-sign regression within ClickHouse’s related moment-family aggregates, while noting that ClickHouse documents the instability and provides a Welford variant. The paper details these representation-driven cases and the vendor-supported alternative.

For evaluators, the practical lesson is narrow but important: a fixed epsilon is not a neutral compromise. Below its crossover it is too permissive; above it, too strict. When benchmark correctness depends on comparing numeric SQL outputs, the tolerance should reflect the data’s conditioning and the engine’s aggregate algorithm—or the verdict should remain indeterminate. The public artifact runs locally with free software, verifies bit-exact input transport and publishes the classifier, engine adapters, generated maps and datasets behind the claims. The repository documents a one-command reproduction and the artifact layout.