Subject: Re: [PATCH] GROUP BY ALL

From: "David G. Johnston" <[email-masked]>
Date: Mon, 22 Jul 2024 14:33:57 -0700

On Mon, Jul 22, 2024 at 1:55 PM David Christensen <[email-masked]> wrote:

> I see that there'd been some chatter but not a lot of discussion about
> a GROUP BY ALL feature/functionality.  There certainly is utility in
> such a construct IMHO.
>
> Still need some docs; just throwing this out there and getting some
> feedback.
>
>
I strongly dislike adding this feature.  I'd only consider supporting it if
it was part of the SQL standard.

Code is written once and read many times.  This feature caters to
the writer, not the reader.  And furthermore usage of this is prone to be
to the writer's detriment as well.

David J.

---

From: Isaac Morland <[email-masked]>
Date: Mon, 22 Jul 2024 17:40:55 -0400

On Mon, 22 Jul 2024 at 17:34, David G. Johnston <[email-masked]>
wrote:

> I strongly dislike adding this feature.  I'd only consider supporting it
> if it was part of the SQL standard.
>

And for when this might be useful, the syntax for it already exists,
although a spurious error message is generated:

odyssey=> select (uw_term).*, count(*) from uw_term group by uw_term;
ERROR:  column "uw_term.term_id" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: select (uw_term).*, count(*) from uw_term group by uw_term;
                ^

I'm not sure exactly what's going on here — it's like it's still seeing the
table name in the field list as only a table name and not the value
corresponding to the whole table as a row value (But in general I'm not
happy with the system's ability to figure out that a column's value has
only one possibility given the grouping columns). You can work around:

odyssey=> with t as (select uw_term, count(*) from uw_term group by
uw_term) select (uw_term).*, count from t;

This query works.

---

From: Tom Lane <[email-masked]>
Date: Mon, 22 Jul 2024 18:43:41 -0400

> I'm not sure exactly what's going on here

The SELECT entry is expanded into "uw_term.col1, uw_term.col2,
uw_term.col3, ...", and those single-column Vars don't match the
whole-row Var appearing in the GROUP BY list.  I guess if we
think this is important, we could add a proof rule saying that
a per-column Var is functionally dependent on a whole-row Var
of the same relation.  Odd that the point hasn't come up before
(though I guess that suggests that few people try this).

			regards, tom lane

---

From: David Christensen <[email-masked]>
Date: Tue, 23 Jul 2024 08:22:55 -0500

On Mon, Jul 22, 2024 at 4:34 PM David G. Johnston
<[email-masked]> wrote:
>> Still need some docs; just throwing this out there and getting some feedback.
>>
>
> I strongly dislike adding this feature.  I'd only consider supporting it if it was part of the SQL standard.
>
> Code is written once and read many times.  This feature caters to the writer, not the reader.  And furthermore usage of this is prone to be to the writer's detriment as well.

I'd say this feature (at least for me) caters to the investigator;
someone who is interactively looking at data hence why it would cater
to the writer.  Consider acquainting yourself with a large table that
has a large number of annoying-named fields where you want to look at
how different data is correlated or broken-down.  Something along the
lines of:

SELECT last_name, substring(first_name,1,1) as first_initial,
income_range, count(*) FROM census_data GROUP BY ALL;

If you are iteratively looking at things, adding or removing fields
from your breakdown, you only need to change it in a single place, the
tlist.  Additionally, expressions can be used transparently without
needing to repeat them.  (Yes, in practice, I'd often use GROUP BY 1,
2, say, but if you add more fields to this you need to edit in
multiple places.)

David

---

From: Paul Jungwirth <[email-masked]>
Date: Tue, 23 Jul 2024 10:02:36 -0700

> (though I guess that suggests that few people try this).

I was just using this group-by-row feature last week to implement a temporal outer join in a way 
that would work for arbitrary tables. Here is some example SQL:

https://github.com/pjungwir/temporal_ops/blob/b10d65323749faa6c47956db2e8f95441e508fce/sql/outer_join.sql#L48-L66

That does `GROUP BY a` then `SELECT (x.a).*`.[1]

It is very useful for writing queries that don't want to know about the structure of the row.

I noticed the same error as Isaac. I worked around the problem by wrapping it in a subquery and 
decomposing the row outside. It's already an obscure feature, and an easy workaround might be why 
you haven't heard complaints before. I wouldn't mind writing a patch for that rule when I get a 
chance (if no one else gets to it first.)

[1] Actually I see it does `GROUP BY a, a.valid_at`, but that is surely more than I need. I think 
that `a.valid_at` is leftover from a previous version of the query.

Yours,

-- 
Paul              ~{:-)
[email-masked]

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 24 Jul 2024 19:42:01 +0530

> how different data is correlated or broken-down.  Something along the

To me this looks like a feature that a data exploration tool may
implement instead of being part of the server. It would then provide
more statistics about each correlation/column set etc.

-- 
Best Wishes,
Ashutosh Bapat