Cross-shard GROUP BY merges rows on selected columns only (distinct groups summed); ORDER BY sum(...) without alias ignored #1821

Summary

When a cross-shard GROUP BY lists columns that are not in the select list, PgDog merges the per-shard rows using only the selected columns. Distinct groups are summed into one row, and the client gets fewer rows with wrong aggregates and no error. Separately, ORDER BY <aggregate expression> without an alias, such as ORDER BY sum(id) DESC, is not applied to the merged result.

Environment

PgDog main @ 8259ed4 (2026-10-14, includes #1810). 4 range shards (id 1–100 / 101–200 / 201–300 / 301–400), PostgreSQL 18.6, [rewrite] enabled = true.

CREATE TABLE t_sharded (id int PRIMARY KEY, v int NOT NULL, s text);   -- sharded on id
INSERT INTO t_sharded SELECT i, i % 7, 'x' || i FROM generate_series(1, 400) i;   -- s is unique

Reproduction

1. Groups merged on the selected columns only:

=> SELECT v, count(*) FROM t_sharded GROUP BY s, v ORDER BY v LIMIT 3;
 v | count
---+-------
 0 |    12          -- PgDog: one row
-- PostgreSQL with the same 400 rows: (0, 1), (0, 1), (0, 1)

Each shard returns its 3 rows (0, 1). PgDog then treats the result as grouped by v, the only selected grouping column, and adds them up into (0, 12).

2. ORDER BY on an unaliased aggregate is ignored:

=> SELECT v, sum(id) FROM t_sharded GROUP BY v ORDER BY sum(id) DESC;
 v |  sum
---+-------
 3 | 11343
 6 | 11514
 5 | 11457
 1 | 11629
 ...                -- not sorted
=> SELECT v, sum(id) AS total FROM t_sharded GROUP BY v ORDER BY total DESC;   -- correct with an alias
 1 | 11629
 0 | 11571
 ...

Found with

CH-benCHmark Q18 over TPC-C data (200 warehouses, 4 shards):

select c_last, c_id o_id, o_entry_d, o_ol_cnt, sum(ol_amount)
from customer, orders, order_line
where c_id = o_c_id and c_w_id = o_w_id and c_d_id = o_d_id
  and ol_w_id = o_w_id and ol_d_id = o_d_id and ol_o_id = o_id
group by o_id, o_w_id, o_d_id, c_id, c_last, o_entry_d, o_ol_cnt
having sum(ol_amount) > 200
order by sum(ol_amount) desc, o_entry_d

Every group lives on one shard, so concatenating the shard results would have been correct. PgDog returned 1,063,915 rows instead of 1,800,000: exactly the number of distinct (c_last, c_id, o_entry_d, o_ol_cnt) values. The grand total of the sum column was unchanged (89,997,096,966.37), confirming the rows were merged. The output was also not ordered by sum(ol_amount) desc (487,816 order violations).

Expected

• The merge key for re-aggregation must be the full GROUP BY list (adding hidden columns for the merge if needed), or, if that isn't supported, an error.
• ORDER BY on an aggregate expression should be matched to its output column, as it already is with an alias.

Minor

Timestamps in merged results come back as 2026-10-13 06:40:28.000000, where PostgreSQL sends 2026-10-13 06:40:28. The value is the same but the text representation differs, which breaks clients that compare strings.