Scalar aggregates without GROUP BY return one row per shard instead of a single merged row; aliased scalar aggregates lose the value entirely

[mkhairi]

Tested against: origin/main @ 8e84501a (post-v0.3.0 main head, 2026-07-07)

Severity: High — every count(*)/SUM(...) without GROUP BY returns a
multi-row result over pgwire; clients that read the first row (which is how
virtually every SQL client consumes a scalar aggregate) get 0/NULL instead
of the real value, silently.

Summary

On a document_strict collection, a scalar aggregate query with no
GROUP BY returns 11 rows — ten identity rows (0 for count,
empty for SUM) plus one row carrying the real value at a
shard-dependent position. The per-shard partials appear to be emitted
without the final merge step. Worse, if the aggregate carries an AS
alias, all 11 rows come back empty — the value is lost entirely.

Grouped aggregates merge correctly, and the same count(*) on a
timeseries collection returns a single correct row, so the defect is
specific to the scalar-aggregate finalize path on the document engine.
This is a regression relative to 67c4572d, and looks like fallout of
the recent change that emits an identity row for scalar aggregates over
zero rows: each shard now contributes its identity row to the client
result instead of folding into one.

Repro

CREATE COLLECTION agg_repro (id TEXT PRIMARY KEY, score FLOAT) WITH (engine='document_strict');
INSERT INTO agg_repro (id, score) VALUES ('r1', 7);
INSERT INTO agg_repro (id, score) VALUES ('r2', 3);

SELECT count(*) FROM agg_repro;
--  count(*)
--  0
--  0
--  0
--  0
--  0
--  0
--  2          -- real value, shard-dependent position
--  0
--  0
--  0
--  0
-- (11 rows)   -- expected: single row, 2

SELECT SUM(score) FROM agg_repro;
-- 10 empty rows + one row '10.0'
-- (11 rows)   -- expected: single row, 10.0

SELECT count(*) AS c FROM agg_repro;
-- (11 rows, ALL empty)   -- alias loses the value entirely; expected: single row, 2

-- control 1: grouped aggregates merge correctly
-- control 2: count(*) on a timeseries collection returns a single correct row

Expected

1. A scalar aggregate returns exactly one row containing the merged
   value across all shards, matching PostgreSQL semantics.
2. An AS alias on the aggregate renames the output column and does
   not affect the value.
3. The zero-rows identity-row behavior still holds (one row, not one
   per shard).

Files (best-guess)

• nodedb/src/data/executor/handlers/accum/finalize.rs — scalar
  accumulator finalize; per-shard identity/partial rows appear to be
  emitted to the client stream instead of being folded into one.
• nodedb/src/data/executor/handlers/aggregate/streaming/finalize.rs —
  same series; the aliased case losing values suggests the alias path
  diverges before the merge.

Operational context

Any monitoring dashboard, pagination total, existence guard, or test
assertion that issues SELECT count(*) FROM ... over pgwire now reads
0 most of the time (whenever the real partial doesn't land in row 1)
with no error raised. This breaks the most common single query shape in
SQL clients while looking like an empty collection rather than a server
defect.