Horton

Re: [PATCH] GROUP BY ALL

Peter Eisentraut <peter@eisentraut.org>

View thread
From: Peter Eisentraut <peter@eisentraut.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, "David G. Johnston" <david.g.johnston@gmail.com>, David Christensen <david@pgguru.net>, Jelte Fennema-Nio <postgres@jeltef.nl>
Date: 2025-09-27T13:30:29Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add GROUP BY ALL.

  2. Refactor to avoid code duplication in transformPLAssignStmt.

  3. Fix missed copying of groupDistinct in transformPLAssignStmt.

On 26.09.25 22:18, Tom Lane wrote:
> Got it, mostly.  There is an edge case, though: what if there are no
> candidate grouping items?  I see these test cases in David's patch:
> 
> +-- oops all aggregates
> +EXPLAIN (COSTS OFF) SELECT COUNT(a), SUM(b) FROM t1 GROUP BY ALL;
> +      QUERY PLAN
> +----------------------
> + Aggregate
> +   ->  Seq Scan on t1
> +(2 rows)
> +
> +-- empty column list
> +EXPLAIN (COSTS OFF) SELECT FROM t1 GROUP BY ALL;
> +   QUERY PLAN
> +----------------
> + Seq Scan on t1
> +(1 row)
> 
> That is, in such cases the patch behaves as if there were no GROUP BY
> clause at all, which seems kinda dubious.  Should this be an error,
> and if not what's it supposed to do?

These should resolve to GROUP BY ().

> Also, what about window functions in the tlist?

> (I didn't stop to figure out why this isn't giving the same error, but
> maybe it's an order-of-checks thing.)  In any case: should this give
> "window functions are not allowed in GROUP BY", or should the
> window-function-containing tlist item be silently skipped by GROUP BY
> ALL?  Trying to make it work is surely not the right answer.

Hmm, I don't know.  The syntactic transformation talks about select list 
elements that "do not directly contain an <aggregate function>", but 
that can also appear as part of <window function>, so the syntactic 
transformation might appear to apply only to some types of window 
functions, which doesn't make sense to me.

I don't know what a sensible behavior should be here.  Maybe in this 
first patch version just reject use of GROUP BY ALL if you find any 
window functions in the select list.