cockroachdb / cockroachdb/cockroach

STDDEV(DECIMAL) is not deterministic across semantically equivalent execution plans: Welford aggregation violates associativity for high-precision DECIMAL

Open
#174,729 4 comments 0 reactions 0 assignees View on GitHub
C-bug O-community T-sql-queries X-blathers-triaged
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

Two semantically equivalent queries compute `STDDEV(vp_computed)` on the same input rows. The only difference is that one query directly scans the base table `src`, while the other reconstructs the same rows via a `MERGE JOIN (SEMI)` between vertically split tables `l` and `r`.
The DECIMAL results differ in the last digit:

- Single-table query (`single`): **4.8989794855663561964** (stable across runs)
- Split/join query (`split`): **4.8989794855663561965** or **...1964** (non-deterministic, varies between runs)

Both queries execute with `distribution: full` according to `EXPLAIN (DISTSQL)`, but the `single` plan streams from a single source and has a deterministic merge path, while the `split` plan involves a join that produces multiple partial aggregation states whose final merge order is non-deterministic.

CockroachDB uses a Welford-style incremental state `(count, mean, M2)` for STDDEV/VARIANCE on DECIMAL. This state involves division and multiplication, and under limited DECIMAL precision it **does not satisfy associativity**. When partial states are merged in different orders, the final DECIMAL result can differ by 1 ULP (or more).
Because DECIMAL is documented as an exact, fixed-point type, and `stddev`/`variance` on DECIMAL input is marked as Immutable (must return the same result in any context), this is a real bug – not an acceptable approximation. The difference of 1e-19 is still a different DECIMAL value and violates the determinism guarantee.

In addition to this main case, we also observed a related variant during testing: `VARIANCE(rowid)` on DECIMAL similarly wobbles between two adjacent values (`...4335` and `...4336`). The single-vs-split difference appears less frequently, but can be observed after repeated execution. The root cause is the same.

**To Reproduce**

**What did you do?**
Ran `STDDEV(vp_computed)` on identical input rows using two different but semantically equivalent query shapes: a direct scan and a join-based reconstruction.

**Steps to reproduce:**
1. Start a three-node CockroachDB v26.2.3 cluster (insecure mode).
2. Connect with psql.
3. Run the following SQL:
```sql
DROP DATABASE IF EXISTS repro_crdb803_db8_min;
CREATE DATABASE repro_crdb803_db8_min;
USE repro_crdb803_db8_min;

SET TimeZone = 'UTC';
SET vectorize = 'on';
SET optimizer_use_histograms = 'on';
SET enable_zigzag_join = 'off';
SET distsql = 'auto';
SET opt_split_scan_limit = 2048;
SET serial_normalization = 'rowid';

-- Single-table schema
CREATE TABLE src (
vp_rowid INT8 PRIMARY KEY,
c0 FLOAT NULL,
vp_computed INT8 AS (vp_rowid * 2) STORED
);

-- Vertically split tables: left l (c0 + vp_computed), right r (c0)
CREATE TABLE l (
vp_rowid INT8 PRIMARY KEY,
c0 FLOAT NULL,
vp_computed INT8 AS (vp_rowid * 2) STORED
);
CREATE TABLE r (
vp_rowid INT8 PRIMARY KEY,
c0 FLOAT NULL
);

INSERT INTO src (vp_rowid, c0) VALUES
(1, -1.370533208e+09),
(2, 0.08560940630775526),
(3, 0.49184326379793697),
(4, 0.619883735554361),
(5, 0.6697518608462573),
(6, 0.6905402975088976),
(7, 2.08893773e+08),
(8, 1.275818667e+09);
INSERT INTO l (vp_rowid, c0) SELECT vp_rowid, c0 FROM src;
INSERT INTO r (vp_rowid, c0) SELECT vp_rowid, c0 FROM src;

CREATE INDEX l_partial ON l(c0) WHERE c0 IS NOT NULL;
CREATE INDEX r_partial ON r(c0) WHERE c0 IS NOT NULL;

-- single: equivalent to constant grouping
SELECT STDDEV(vp_computed) FROM src GROUP BY CAST(NULL AS INT8);

-- split: reconstruct the same rows via semi join
SELECT STDDEV(vp_computed) FROM (
SELECT l.vp_rowid, l.c0, l.vp_computed
FROM l
WHERE EXISTS (SELECT 1 FROM r WHERE r.vp_rowid = l.vp_rowid)
) AS src GROUP BY CAST(NULL AS INT8);
```
4. Run the two queries multiple times. Observe that `single` is stable at `4.8989794855663561964`, while `split` fluctuates between `4.8989794855663561964` and `4.8989794855663561965`.

**Expected behavior**
Both queries must return **exactly the same DECIMAL value**. Since `STDDEV(DECIMAL)` is documented as Immutable and DECIMAL is an exact type, the result must be deterministic and independent of the execution plan (single scan vs. join reconstruction). A difference of even 1 ULP is a bug.

**Environment:**
- **CockroachDB version:** CockroachDB CCL v26.2.3
- **Docker image:** cockroachdb/cockroach:v26.2.3
- **Server OS:** Ubuntu 20.04.6 LTS x86_64, Linux 5.4.0-204-generic
- **Client app:** psql (PostgreSQL connection, sslmode=disable)
- **Cluster mode:** three-node, insecure. Both queries use `distribution: full`; the split plan introduces a merge join that changes the aggregation merge order.

Jira issue: CRDB-67940

Contributor guide

Open the contributing guide

Research direction

Reproduce the two queries on the described three-node CockroachDB v26.2.3 setup, then trace the DECIMAL STDDEV/VARIANCE Welford aggregation and its partial-state merge behavior. Done means semantically equivalent plans return the same exact DECIMAL value consistently, including the reported STDDEV and VARIANCE cases.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases, distributed-systems
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.