pingcap / pingcap/tidb

`STDDEV_POP` / `VAR_*` over `BIGINT UNSIGNED` near `2^64` wrong on `UNION ALL`

Open
#70,400 3 comments 0 reactions 0 assignees View on GitHub
affects-7.5 affects-8.1 affects-8.5 contribution severity/major sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

On a base table (or a materialized copy of a `UNION ALL`), TiDB correctly returns `0.0`. Over a **≥3-way `UNION ALL`** of the same rows (e.g. `MOD(sh,4)=0/1/2/3` partitions), the same aggregates return a **nonzero** value (`STDDEV_POP` ≈ `443.405`). A 2-way `UNION ALL` still returns `0.0`. MySQL 9.7.2 and MariaDB 11.4 return `0.0` for the 4-way form.

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE u (sh BIGINT UNSIGNED);
INSERT INTO u VALUES
(18446744073709551614),
(18446744073709551613),
(18446744073709551612),
(18446744073709551611),
(18446744073709551610),
(18446744073709551609),
(18446744073709551608),
(18446744073709551607);

SELECT STDDEV_POP(sh) FROM u;
-- 0.0

SELECT STDDEV_POP(sh) FROM (
SELECT sh FROM u WHERE MOD(sh, 4) = 0
UNION ALL SELECT sh FROM u WHERE MOD(sh, 4) = 1
UNION ALL SELECT sh FROM u WHERE MOD(sh, 4) = 2
UNION ALL SELECT sh FROM u WHERE MOD(sh, 4) = 3
) AS x;
-- 443.40500673763256
```

### 2. What did you expect to see? (Required)

### 3. What did you see instead (Required)

### 4. What is your TiDB version? (Required)

`tidb 8.0.11-TiDB-v8.5.0`

Contributor guide

Open the contributing guide

Research direction

Start by running the provided SQL reproduction and compare the base-table result with the four-way UNION ALL result, then inspect the database aggregation and UNION ALL execution paths. Done means STDDEV_POP and the related VAR_* aggregates return 0.0 for the reproduced BIGINT UNSIGNED values, including the four-way UNION ALL query.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.