Feedback-ID: ie0a040ee:Fastmail
Message-ID: <87b40ca1-f935-4d71-9edb-9d9f1053cb45@eisentraut.org>
Date: Sat, 27 Sep 2025 15:30:29 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PATCH] GROUP BY ALL
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>
References: 
 <CAHM0NXjz0kDwtzoe-fnHAqPB1qA8_VJN0XAmCgUZ+iPnvP5LbA@mail.gmail.com>
 <CAKFQuwY0vhNG8T+pC3BQJurFi3NN_L1KbEPGagRN3V5nKZcpDQ@mail.gmail.com>
 <931747.1721687375@sss.pgh.pa.us>
 <b46070c0-0e98-49a0-823f-fa7e13bf626b@eisentraut.org>
 <CAGECzQSGKWy1tcB8BB=p2suOvW0KLtLR+pm=XTagfp9WSA4HXQ@mail.gmail.com>
 <634aca95-6db5-4beb-b18d-67e65582817f@eisentraut.org>
 <4054709.1758895915@sss.pgh.pa.us>
 <3d01eb20-d587-4dec-9261-0c92240ce546@eisentraut.org>
 <4174779.1758917912@sss.pgh.pa.us>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <4174779.1758917912@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/87b40ca1-f935-4d71-9edb-9d9f1053cb45%40eisentraut.org>
Precedence: bulk

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.