Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1v2XOP-004yk9-JE
	for pgsql-hackers@arkaria.postgresql.org; Sat, 27 Sep 2025 16:03:25 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1v2XON-00Avcf-FO
	for pgsql-hackers@arkaria.postgresql.org; Sat, 27 Sep 2025 16:03:24 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1v2XON-00AvcW-6A
	for pgsql-hackers@lists.postgresql.org; Sat, 27 Sep 2025 16:03:23 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1v2XOL-000DAz-1E
	for pgsql-hackers@postgresql.org;
	Sat, 27 Sep 2025 16:03:22 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 58RG3AvO167506;
	Sat, 27 Sep 2025 12:03:10 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter@eisentraut.org>
cc: David Christensen <david@pgguru.net>,
        Andrey Borodin <x4mmm@yandex-team.ru>,
        pgsql-hackers <pgsql-hackers@postgresql.org>,
        "David G. Johnston" <david.g.johnston@gmail.com>,
        Jelte Fennema-Nio <postgres@jeltef.nl>
Subject: Re: [PATCH] GROUP BY ALL
In-reply-to: <cf397a8c-49cf-4839-9a3f-992ea49feb12@eisentraut.org>
References: <4D2047B0-E8D8-472B-B7E8-61206B1E6AFA@yandex-team.ru>
 <F694DABC-6A42-4614-B254-643180B9D6F6@pgguru.net>
 <CAHM0NXj1Gv_96WxZkiLKhxJVaYOGcmdZ2hApNcvWQ5HxV84Q=A@mail.gmail.com>
 <4083063.1758902694@sss.pgh.pa.us>
 <CAHM0NXgPhe_OF81MyA9ocWZ=b+oEAHtQHoDXw4DverPdbkXzpg@mail.gmail.com>
 <4085064.1758903815@sss.pgh.pa.us>
 <cf397a8c-49cf-4839-9a3f-992ea49feb12@eisentraut.org>
Comments: In-reply-to Peter Eisentraut <peter@eisentraut.org>
	message dated "Sat, 27 Sep 2025 15:40:05 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <167504.1758988990.1@sss.pgh.pa.us>
Date: Sat, 27 Sep 2025 12:03:10 -0400
Message-ID: <167505.1758988990@sss.pgh.pa.us>
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/167505.1758988990%40sss.pgh.pa.us>
Precedence: bulk

Peter Eisentraut <peter@eisentraut.org> writes:
> The language used in the standard at the moment is the select list 
> elements that "do not directly contain an <aggregate function>", where 
> "directly contain" is a term of art that means "contains without an 
> intervening instance of <subquery>, <within group specification>, or 
> <set function specification> that is not an <ordered set function>".  So 
> it means not to look into subqueries.

TBH, that is obvious nonsense.  A subquery could contain an aggregate
function that we've already identified as being of the current query
level.  Putting such a construct into the GROUP BY list would create
an invalid query (cf. checkTargetlistEntrySQL92).  Similarly, putting
a window function into the GROUP BY list would create an invalid
query.

> Note that in standard SQL, the GROUP BY clause can only contain plain 
> column references, not expressions, so this question is kind of moot in 
> that context, because the query would be invalid no matter whether you 
> transform the GROUP BY ALL to group by the subquery or not.

So according to the standard, this:

	select a+b, count(*) from ... group by all;

would be invalid because a+b couldn't be written directly in
GROUP BY?  I can't see us rejecting that though, since we do
allow a+b in GROUP BY.

Seems like we're getting very little help from the standard as to
what this construct actually means.  I suggest that we ignore the
current draft as not having been thought through quite enough yet,
and make ALL skip any tlist entries that contain_aggs_of_level
zero or contain_windowfuncs.  If that means we're extending the
standard, so be it --- we've already extended GROUP BY quite a lot,
it seems.

			regards, tom lane