MIME-Version: 1.0
References: <CAHM0NXjz0kDwtzoe-fnHAqPB1qA8_VJN0XAmCgUZ+iPnvP5LbA@mail.gmail.com>
 <CAKFQuwY0vhNG8T+pC3BQJurFi3NN_L1KbEPGagRN3V5nKZcpDQ@mail.gmail.com>
In-Reply-To: <CAKFQuwY0vhNG8T+pC3BQJurFi3NN_L1KbEPGagRN3V5nKZcpDQ@mail.gmail.com>
From: Isaac Morland <isaac.morland@gmail.com>
Date: Mon, 22 Jul 2024 17:40:55 -0400
Message-ID: <CAMsGm5eUihhgcNqDr1h=PwRZxt=3GUnOHqAhvam8X7j2jioaeQ@mail.gmail.com>
Subject: Re: [PATCH] GROUP BY ALL
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: David Christensen <david@pgguru.net>, pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000204b94061ddce727"
Archived-At: <https://www.postgresql.org/message-id/CAMsGm5eUihhgcNqDr1h%3DPwRZxt%3D3GUnOHqAhvam8X7j2jioaeQ%40mail.gmail.com>
Precedence: bulk

--000000000000204b94061ddce727
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, 22 Jul 2024 at 17:34, David G. Johnston <david.g.johnston@gmail.com=
>
wrote:

> On Mon, Jul 22, 2024 at 1:55=E2=80=AFPM David Christensen <david@pgguru.n=
et>
> 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.
>

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

odyssey=3D> 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 =E2=80=94 it's like it's still se=
eing 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=3D> with t as (select uw_term, count(*) from uw_term group by
uw_term) select (uw_term).*, count from t;

This query works.

--000000000000204b94061ddce727
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, 22 Jul 2024 at 17:34, David G. Jo=
hnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.johnston@g=
mail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;=
border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex=
"><div dir=3D"ltr"><div dir=3D"ltr"><div style=3D"font-family:arial,helveti=
ca,sans-serif"><span style=3D"font-family:Arial,Helvetica,sans-serif">On Mo=
n, Jul 22, 2024 at 1:55=E2=80=AFPM David Christensen &lt;<a href=3D"mailto:=
david@pgguru.net" target=3D"_blank">david@pgguru.net</a>&gt; wrote:</span><=
br></div></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:=
solid;border-left-color:rgb(204,204,204);padding-left:1ex">I see that there=
&#39;d been some chatter but not a lot of discussion about<br>
a GROUP BY ALL feature/functionality.=C2=A0 There certainly is utility in<b=
r>
such a construct IMHO.<br><br>
Still need some docs; just throwing this out there and getting some feedbac=
k.<br><br></blockquote><div><br></div><div style=3D"font-family:arial,helve=
tica,sans-serif">I strongly dislike adding this feature.=C2=A0 I&#39;d only=
 consider supporting it if it was part of the SQL standard.</div><div style=
=3D"font-family:arial,helvetica,sans-serif"><br></div><div style=3D"font-fa=
mily:arial,helvetica,sans-serif">Code is written once and read many=C2=A0ti=
mes.=C2=A0 This feature caters to the=C2=A0writer, not the=C2=A0reader.=C2=
=A0 And furthermore usage of this is prone to be to the writer&#39;s detrim=
ent as well.</div></div></div></blockquote><div><br></div><div>And for when=
 this might be useful, the syntax for it already exists, although a spuriou=
s error message is generated:</div><div><br></div><div>odyssey=3D&gt; selec=
t (uw_term).*, count(*) from uw_term group by uw_term;<br>ERROR: =C2=A0colu=
mn &quot;uw_term.term_id&quot; must appear in the GROUP BY clause or be use=
d in an aggregate function<br>LINE 1: select (uw_term).*, count(*) from uw_=
term group by uw_term;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 ^<br></div><div><br></div><div>I&#39;m not sure exactly what&#39;s g=
oing on here =E2=80=94 it&#39;s like it&#39;s still seeing the table name i=
n the field list as only a table name and not the value corresponding to th=
e whole table as a row value (But in general I&#39;m not happy with the sys=
tem&#39;s ability to figure out that a column&#39;s value has only one poss=
ibility given the grouping columns). You can work around:</div><div><br></d=
iv><div>odyssey=3D&gt; with t as (select uw_term, count(*) from uw_term gro=
up by uw_term) select (uw_term).*, count from t;<br></div><div><br></div><d=
iv>This query works.</div></div></div>

--000000000000204b94061ddce727--