Subject: GROUP BY ALL

From: Andrey Borodin <[email-masked]>
Date: Sun, 18 Dec 2022 20:19:10 -0800

Hi hackers!

I saw a thread in a social network[0] about GROUP BY ALL. The idea seems useful.
I always was writing something like
    select datname, usename, count(*) from pg_stat_activity group by 1,2;
and then rewriting to
    select datname, usename, query, count(*) from pg_stat_activity group by 1,2;
and then "aaahhhh, add a number at the end".

With the proposed feature I can write just
    select datname, usename, count(*) from pg_stat_activity group by all;

PFA very dummy implementation just for a discussion. I think we can
add all non-aggregating targets.

What do you think?

Best regards, Andrey Borodin.

[0] https://www.linkedin.com/posts/mosha_duckdb-firebolt-snowflake-activity-7009615821006131200-VQ0o/

---

From: Tom Lane <[email-masked]>
Date: Sun, 18 Dec 2022 23:30:14 -0500

> I saw a thread in a social network[0] about GROUP BY ALL. The idea seems useful.

Isn't that just a nonstandard spelling of SELECT DISTINCT?

What would happen if there are aggregate functions in the tlist?
I'm not especially on board with "ALL" meaning "ALL (oh, but not
aggregates)".

			regards, tom lane

---

From: Andrey Borodin <[email-masked]>
Date: Sun, 18 Dec 2022 20:40:00 -0800

> I'm not especially on board with "ALL" meaning "ALL (oh, but not

Yes, that's the weak part of the proposal. I even thought about
renaming it to "GROUP BY SOMEHOW" or even "GROUP BY SURPRISE ME".
I mean I see some cases when it's useful and much less cases when it's
dangerously ambiguous. E.g. grouping by result of a subquery looks way
too complex and unpredictable. But with simple Vars... what could go
wrong?

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 18 Dec 2022 21:45:58 -0700

> I'm not especially on board with "ALL" meaning "ALL (oh, but not
IIUC some systems treat any non-aggregated column as an implicit group by
column.  This proposal is an explicit way to enable that implicit behavior
in PostgreSQL.  It is, as you note, an odd meaning for the word ALL.

We tend to not accept non-standard usability syntax extensions even if
others systems implement them.  I don’t see this one ending up being an
exception…

David J.

---

From: Isaac Morland <[email-masked]>
Date: Mon, 19 Dec 2022 08:44:39 -0500

> Isn't that just a nonstandard spelling of SELECT DISTINCT?

In a pure relational system, yes; but since Postgres allows duplicate rows,
both in actual table data and in intermediate and final result sets, no.
Although I'm pretty sure no aggregates other than count() are useful - any
other aggregate would always just combine count() copies of the duplicated
value in some way.

> I'm not especially on board with "ALL" meaning "ALL (oh, but not

The requested behaviour can be accomplished by an invocation something like:

select (t).*, count(*) from (select (…field1, field2, …) as t from
…tables…) s group by t;

So we collect all the required fields as a tuple, group by the tuple, and
then unpack it into separate columns in the outer query.

---

From: Vik Fearing <[email-masked]>
Date: Mon, 19 Dec 2022 17:53:46 +0100

>      select datname, usename, count(*) from pg_stat_activity group by all;

We already have GROUP BY ALL, but it doesn't do this.

> add all non-aggregating targets.

I think this is a pretty terrible idea.  If we want that kind of 
behavior, we should just allow the GROUP BY to be omitted since without 
grouping sets, it is kind of redundant anyway.

I don't know what my opinion is on that.
-- 
Vik Fearing