sql: row-engine windower computes sliding-frame `sum`/`avg` over INT8 wrong by 2^64 after `-9223372036854775808` leaves the frame (negation overflow)

Aperta
#176,237 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
3/5
Tempo stimato
1-2 giorni
Idoneità per principianti
70/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
go, postgresql, sql
Ambito
backend, databases

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

C-bug O-community T-sql-queries X-blathers-triaged X-move-internal

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

  1. cockroach start-single-node --insecure
  2. 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

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di cockroachdb/cockroach

Tutte le issue di cockroachdb/cockroach

Issue simili

Altre issue su Go

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.