Feedback-ID: ie0a040ee:Fastmail
Message-ID: <cf397a8c-49cf-4839-9a3f-992ea49feb12@eisentraut.org>
Date: Sat, 27 Sep 2025 15:40:05 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PATCH] GROUP BY ALL
To: Tom Lane <tgl@sss.pgh.pa.us>, David Christensen <david@pgguru.net>
Cc: 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>
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>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <4085064.1758903815@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/cf397a8c-49cf-4839-9a3f-992ea49feb12%40eisentraut.org>
Precedence: bulk

On 26.09.25 18:23, Tom Lane wrote:
> No, I think the correct behavior would have to be to descend into
> SubLinks to see if they contain any aggregates belonging to the
> outer query level.
> 
> However (looks around) we do already have that code.
> See contain_aggs_of_level.  (contain_agg_clause is essentially
> a simplified version that is okay to use in the planner because
> it's already gotten rid of sublinks.)
> 
> What mainly concerns me at this point is whether we've identified
> aggregate levels at the point in parsing where you want to run this.
> I have a bit of a worry that that might interact with grouping.
> Presumably the SQL committee thought about that, so it's probably
> soluble, but ...

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.

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.

For this first patch version, I suggest you reject the use of GROUP BY 
ALL if you find a subquery in the select list, unless you have an 
unambiguous better solution.

(It was discussed to relax this restriction, so this discussion is 
useful to collect some questions related to that.)