Feedback-ID: ie0a040ee:Fastmail
Message-ID: <634aca95-6db5-4beb-b18d-67e65582817f@eisentraut.org>
Date: Fri, 26 Sep 2025 09:02:08 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PATCH] GROUP BY ALL
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 "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>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: 
 <CAGECzQSGKWy1tcB8BB=p2suOvW0KLtLR+pm=XTagfp9WSA4HXQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/634aca95-6db5-4beb-b18d-67e65582817f%40eisentraut.org>
Precedence: bulk

On 17.08.25 19:12, Jelte Fennema-Nio wrote:
> On Tue, 23 Jul 2024 at 22:02, Peter Eisentraut <peter@eisentraut.org> wrote:
>> Looks like the main existing implementations take it to mean all entries
>> in the SELECT list that are not aggregate functions.
>>
>> https://duckdb.org/docs/sql/query_syntax/groupby.html#group-by-all
>> https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-groupby.html#parameters
>> https://docs.snowflake.com/en/sql-reference/constructs/group-by#parameters
> 
> Oracle added support for GROUP BY ALL too now:
> https://danischnider.wordpress.com/2025/08/05/oracle-23-9-supports-group-by-all/

The proposal for GROUP BY ALL was accepted into the SQL standard draft 
yesterday.  So maybe someone wants to take this up again.

The initially proposed patch appears to have the right idea overall. 
But it does not handle more complex cases like

     SELECT a, SUM(b)+a FROM t1 GROUP BY ALL;

correctly.  The piece of code that does

     if (!IsA(n->expr,Aggref))

should be generalized to check for aggregates not only at the top level.

(For explanation:  GROUP BY ALL expands to all select list entries that 
do not contain aggregates.  So the above would expand to

     SELECT a, SUM(b)+a FROM t1 GROUP BY a;

which should then be rejected based on the existing rules.)