sql: row-engine windower computes sliding-frame `sum`/`avg` over INT8 wrong by 2^64 after `-9223372036854775808` leaves the frame (negation overflow)
Nessuno ha ancora preso questa issue.
Valutazione
- Difficoltà
- 3/5
- Tempo stimato
- 1-2 giorni
- Idoneità per principianti
- 70/100
Direzione di ricerca
Start with slidingWindowSumFunc.removeAllBefore in pkg/sql/sem/builtins/window_frame_builtins.go and trace how it removes values from the running sum; also read the INT sum overflow handling in pkg/sql/sem/builtins/aggregate_builtins.go. Add regression coverage for sliding ROWS, RANGE, or GROUPS frames containing math.MinInt64, including the row windower path. Done when sum and avg return the expected results after that value leaves the frame.
Scritto dal modello di indicizzazione a partire dal testo della issue.
Descrizione
Describe the problem
For a sliding frame (ROWS/RANGE/GROUPS ... n PRECEDING), the row-engine windower keeps a running
sum and, when the frame start moves past a row, adds the negated value of that row. For an INT8 the
negation is done in Go's int64, where -(-9223372036854775808) wraps to -9223372036854775808. The
row is therefore added a second time instead of being removed. Every later frame of the partition is
off by -18446744073709551616 (-2^64), and avg divides the same wrong sum.
The vectorized windower computes the same frames correctly. However, all window functions over the
same window run in one windower, and the row windower is used by default as soon as one of them is not
supported by the vectorized engine. This happens with array_agg, string_agg, json_agg, bit_and,
variance, corr or a FILTER clause. It also happens with vectorize = off. No error is reported. In
SQL, negating this value fails with integer out of range.
To Reproduce
cockroach start-single-node --insecure- In
cockroach sql --insecure(default settings):
CREATE TABLE readings (id INT PRIMARY KEY, v INT8);
INSERT INTO readings VALUES (1, 5), (2, -9223372036854775808), (3, 7), (4, 1), (5, 2), (6, 3), (7, 4), (8, 6);
SELECT id, v, sum(v) OVER w AS sum, array_agg(v) OVER w AS frame
FROM readings WINDOW w AS (ORDER BY id ROWS 2 PRECEDING) ORDER BY id;
Output on master (identical on v26.3.5):
id | v | sum | frame
-----+----------------------+-----------------------+-----------------------------
1 | 5 | 5 | {5}
2 | -9223372036854775808 | -9223372036854775803 | {5,-9223372036854775808}
3 | 7 | -9223372036854775796 | {5,-9223372036854775808,7}
4 | 1 | -9223372036854775800 | {-9223372036854775808,7,1}
5 | 2 | -18446744073709551606 | {7,1,2}
6 | 3 | -18446744073709551610 | {1,2,3}
7 | 4 | -18446744073709551607 | {2,3,4}
8 | 6 | -18446744073709551603 | {3,4,6}
(8 rows)
From id 5 on, the frame no longer contains the minimum (frame shows {7,1,2} ... {3,4,6}), but the
sums are 10 - 2^64, 6 - 2^64, 9 - 2^64 and 13 - 2^64.
The same sum alone on the window runs in the vectorized windower and is correct:
SELECT id, sum(v) OVER (ORDER BY id ROWS 2 PRECEDING) AS sum FROM readings ORDER BY id;
-- ids 1-8: 5, -9223372036854775803, -9223372036854775796, -9223372036854775800, 10, 6, 9, 13
SET vectorize = off;
SELECT id, sum(v) OVER (ORDER BY id ROWS 2 PRECEDING) AS sum FROM readings ORDER BY id;
-- ids 5-8: -18446744073709551606, -18446744073709551610, -18446744073709551607, -18446744073709551603
SET vectorize = on;
EXPLAIN (VEC) shows which windower runs:
EXPLAIN (VEC) SELECT id, sum(v) OVER w, array_agg(v) OVER w FROM readings WINDOW w AS (ORDER BY id ROWS 2 PRECEDING);
-- └ Node 1
-- └ *rowexec.windower
-- └ *colfetcher.ColBatchScan
EXPLAIN (VEC) SELECT id, sum(v) OVER (ORDER BY id ROWS 2 PRECEDING) FROM readings;
-- └ Node 1
-- └ *colexecwindow.bufferedWindowOp
-- └ *colexec.sortOp
-- └ *colfetcher.ColBatchScan
avg with a FILTER clause (row windower by default) returns averages far below every value in the
frame:
SELECT id, avg(v) FILTER (WHERE v IS NOT NULL) OVER (ORDER BY id ROWS 2 PRECEDING) AS avg FROM readings ORDER BY id;
id | avg
-----+-------------------------
1 | 5.0000000000000000000
2 | -4611686018427387901.5
3 | -3074457345618258598.7
4 | -3074457345618258600.0
5 | -6148914691236517202.0
6 | -6148914691236517203.3
7 | -6148914691236517202.3
8 | -6148914691236517201.0
The wrong values are stored by CREATE TABLE AS:
CREATE TABLE moving AS SELECT id, sum(v) OVER w AS s, array_agg(v) OVER w AS frame FROM readings
WINDOW w AS (ORDER BY id ROWS 2 PRECEDING);
SELECT s, frame FROM moving WHERE id = 8;
-- -18446744073709551603 | {3,4,6}
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW and GROUPS 2 PRECEDING frames give the same wrong sums.
Expected behavior
Each frame's sum is the sum of the values in the frame: 10, 6, 9 and 13 for ids 5-8, as the vectorized
windower returns. PostgreSQL (20devel) returns these sums for the same query with array_agg, and
averages 3.3333333333333333, 2.0000000000000000, 3.0000000000000000 and 4.3333333333333333 for ids 5-8.
Additional data / screenshots
Cause (permalink at d30c905fff79, the current public master HEAD; the master build tested gives the
output above): slidingWindowSumFunc.removeAllBefore removes a row from the running sum by adding its
negation, computed in int64:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/sem/builtins/window_frame_builtins.go#L245-L247
switch v := value.(type) {
case *tree.DInt:
err = w.agg.Add(ctx, tree.NewDInt(-*v))
For *v == math.MinInt64, -*v is math.MinInt64. The INT sum aggregate switches from int64 to an
exact DECIMAL accumulator when an addition overflows
(https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/sem/builtins/aggregate_builtins.go#L3761-L3797),
so adding the value a second time yields an exact result that is 2^64 too small rather than an error,
and the error stays in the running sum for the rest of the partition. avgWindowFunc divides the same
running sum:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/sem/builtins/window_frame_builtins.go#L354-L363
A possible fix: remove math.MinInt64 without the int64 negation, for example by adding the negated
value as a DECIMAL.
Environment:
- CockroachDB version: master edge build v27.1.0-alpha.00000000-dev-fc36d2fe578aab6d0c861e46403f029f938c5a5a (built 2026-11-08) and v26.3.5; the main reproduction gives identical output on both (the additional checks were run on master)
- Server OS: Linux x86_64 (Ubuntu 22.04), single node,
--insecure - Client app:
cockroach sql, psycopg2
Additional context
-9223372036854775808 is a common sentinel value (for example Go's math.MinInt64). Any moving sum or
moving average over such a column is wrong for the rest of the partition once the value has left the
frame, and the result depends on which other window functions are computed alongside it. Results
written with CREATE TABLE AS or INSERT ... SELECT keep the wrong values. No error is returned.
Jira issue: CRDB-71984
- Lingua principale
- Go
- Stelle
- 32.6k
- Fork
- 4.2k
- Metriche di merge delle PR
- Nessuna PR unita negli ultimi 30g
Preparare l'ambiente
- Nessun Dockerfile né file Docker Compose
- Nessun modello di pull request
- Leggi la guida per i contributori
Come iniziare
- Leggi tutta la issue e poi la guida ai contributi del progetto.
- Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
- Fai un fork del repository e lavora su un branch.
- Apri una pull request che faccia riferimento al numero della issue.
Altre issue di cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
cockroachdb/cockroach#176245 · 1 commento ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinForse già presa @1AbdullahButt l’ha presa 14 giorni fa. ApertaC-bug O-community X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
cockroachdb/cockroach#176228 · 1 commento ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEForse di nuovo libera @darshpatel1052 l’ha presa 18 giorni fa e non c’è nessuna pull request aperta. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 62/100
cockroachdb/cockroach#176223 · 2 commenti ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutForse già presa @dajiaohuang l’ha presa 15 giorni fa. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 1/5 Meno di un'ora Idoneità per principianti 84/100
cockroachdb/cockroach#176210 · 1 commento ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
cockroachdb/cockroach#176150 · 1 commento ·
Tutte le issue di cockroachdb/cockroach
Issue simili
-
☢️ Bug v3
Difficoltà 2/5 1-3 ore Idoneità per principianti 82/100
I maintainer di solito rispondono entro 1 giorno
-
area/import-export area/projects concern/ux
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
go-vikunja/vikunja#4455 · 1 commento ·
I maintainer di solito rispondono entro 2 giorni
-
Dependabot PRs no longer merge automaticallyForse già presa @dwisiswant0 l’ha presa oggi. Aperta
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
projectdiscovery/urlfinder#160 ·
I maintainer di solito rispondono entro 1 giorno
-
database
Difficoltà 2/5 1-3 ore Idoneità per principianti 72/100
chnm/legal-modernism#450 ·
I maintainer di solito rispondono entro 1 giorno
-
tech-debt
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
I maintainer di solito rispondono entro 1 giorno