Feedback-ID: ie0a040ee:Fastmail
Message-ID: <49d5cbd1-7e47-4740-bc81-e574150f44cb@eisentraut.org>
Date: Tue, 14 Apr 2026 13:21:30 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PATCH] GROUP BY ALL
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>
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>
 <87b40ca1-f935-4d71-9edb-9d9f1053cb45@eisentraut.org>
Content-Language: en-US
In-Reply-To: <87b40ca1-f935-4d71-9edb-9d9f1053cb45@eisentraut.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/49d5cbd1-7e47-4740-bc81-e574150f44cb%40eisentraut.org>
Precedence: bulk

On 27.09.25 15:30, Peter Eisentraut wrote:
>> 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.

The handling of window functions by GROUP BY ALL is a semi-open-item.

The code in transformGroupClause() currently says:

     /*
      * Likewise, TLEs containing window functions are not okay to add
      * to GROUP BY.  At this writing, the SQL standard is silent on
      * what to do with them, but by analogy to aggregates we'll just
      * skip them.
      */
     if (pstate->p_hasWindowFuncs &&
         contain_windowfuncs((Node *) tle->expr))
         continue;

The wording of the SQL standard currently does not address that at all 
(but we could fix it), which would mean that a window function ends up 
in the GROUP BY ALL expansion by default.

Personally, I don't understand what the meaning of this should be. 
Aggregates relate to grouping, but window functions are a different 
processing phase, so that do they have to do with grouping?

I don't see any mention of using GROUP BY with window functions in our 
relevant documentation, for example

https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
https://www.postgresql.org/docs/devel/functions-window.html

Commit ef38a4d9756 added a regression test

EXPLAIN (COSTS OFF) SELECT a, COUNT(a) OVER (PARTITION BY a) FROM t1 
GROUP BY ALL;

but the test table contains no data, so I don't know if this kind of 
query produces interesting information.   Wouldn't a more practical 
query use different columns, like

SELECT a, COUNT(b) OVER (PARTITION BY a) FROM t1

?

I see that DuckDB and Oracle (the two other implementations that can be 
accessed relatively freely, though there are others) each behave 
differently here.

Maybe we can produce some more test cases to see what useful behaviors 
should be?