public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: David Christensen <[email protected]>
Cc: Jelte Fennema-Nio <[email protected]>
Subject: Re: [PATCH] GROUP BY ALL
Date: Fri, 26 Sep 2025 16:18:32 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAHM0NXjz0kDwtzoe-fnHAqPB1qA8_VJN0XAmCgUZ+iPnvP5LbA@mail.gmail.com>
	<CAKFQuwY0vhNG8T+pC3BQJurFi3NN_L1KbEPGagRN3V5nKZcpDQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAGECzQSGKWy1tcB8BB=p2suOvW0KLtLR+pm=XTagfp9WSA4HXQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Peter Eisentraut <[email protected]> writes:
> On 26.09.25 16:11, Tom Lane wrote:
>> I thought I understood this definition, up till your last
>> comment.  What's invalid about that expanded query?

> This was a sloppy example.  Here is a better one:
>      create table t1 (a int, b int, c int);
>      select a, sum(b)+c from t1 group by all;
> This is equivalent to
>      select a, sum(b)+c from t1 group by a;
> which would be rejected as
>      ERROR:  column "t1.c" must appear in the GROUP BY clause or be used
>      in an aggregate function

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?  The second case is outside the
SQL spec so I'm not expecting guidance on that, but surely the
committee thought about the first case.

Also, what about window functions in the tlist?  If you do

regression=# explain select sum(q1) over(partition by q2) from int8_tbl group by 1;

you get 

ERROR:  window functions are not allowed in GROUP BY
LINE 1: explain select sum(q1) over(partition by q2) from int8_tbl g...
                       ^

but that's not what is happening with

regression=# explain select sum(q1) over(partition by q2) from int8_tbl group by all;
ERROR:  column "int8_tbl.q2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: explain select sum(q1) over(partition by q2) from int8_tbl g...
                                                 ^

(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.

			regards, tom lane

view thread (49+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [PATCH] GROUP BY ALL
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox