Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1qeFj1-006GCr-82
	for pgsql-docs@arkaria.postgresql.org; Thu, 07 Sep 2023 14:11:15 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1qeFiz-002M5v-6T
	for pgsql-docs@arkaria.postgresql.org; Thu, 07 Sep 2023 14:11:12 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <bruce@momjian.us>)
	id 1qeFiy-002M5n-KT
	for pgsql-docs@lists.postgresql.org; Thu, 07 Sep 2023 14:11:12 +0000
Received: from momjian.us ([72.94.173.45])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <bruce@momjian.us>)
	id 1qeFiq-003GIj-MS
	for pgsql-docs@lists.postgresql.org; Thu, 07 Sep 2023 14:11:11 +0000
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=momjian.us;
	s=2023062407; h=In-Reply-To:Content-Type:MIME-Version:References:Message-ID:
	Subject:Cc:To:From:Date:Sender:Reply-To:Content-Transfer-Encoding:Content-ID:
	Content-Description; bh=F1ka+mtx2vNNMkTuNEeCGlD4yRcrDdtGFlU/lQ/BrFw=; b=wfbzQ
	58lCZ2FBe8WZJPA+pH7ay3vKfzszvU8ljXaaXwEWEgXbjzNIFMwTU6SFCt0ba7mqMPt9eviV6Ns+q
	NJDebJGHxWQNaqwdRhCm3Tlyzv1qOudhxxG7f/ku23EEonSJLfh9lMOctQDfd2Euljtq1S+y3zHss
	bd4gXQYsTdKMIflbtxgx1bdvHQRlluTlun9Me06HgtF2/5zrIhVQ5SFP8pYopepRkRZLQ8M5UoJm4
	LsqiA33n+ROQdXlgBA/6T3eAFcyX+LyS39L7nf9SHRaPiNPCSody1/B3x3b1m2iM4vzdlgIw7X5MC
	9Aj3GGBL2pTtzJvto1nANk87tNerA==;
Received: from bruce by momjian.us with local (Exim 4.96)
	(envelope-from <bruce@momjian.us>)
	id 1qeFim-00AjO1-1i;
	Thu, 07 Sep 2023 10:11:00 -0400
Date: Thu, 7 Sep 2023 10:11:00 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	Laurenz Albe <laurenz.albe@cybertec.at>, postgres-hape@gmx.de,
	pgsql-docs@lists.postgresql.org
Subject: Re: group by can use alias from select list
Message-ID: <ZPnZ9ILOZcWaqoY0@momjian.us>
References: <168871536004.379168.9352636188330923805@wrigleys.postgresql.org>
 <25b70e6ec7bef81acac508dc61dfa01e72c96252.camel@cybertec.at>
 <CAKFQuwYALfM-yamV7GCPG_WZvi_qK5O8rJPOGkD40ShDd9M_GQ@mail.gmail.com>
 <ZPkQhuV8I6ijH+y3@momjian.us>
 <1591985.1694044890@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="TmhkXvxWhTPx/HiP"
Content-Disposition: inline
In-Reply-To: <1591985.1694044890@sss.pgh.pa.us>
List-Id: <pgsql-docs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-docs@lists.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-docs>
Archived-At: 
 <https://www.postgresql.org/message-id/ZPnZ9ILOZcWaqoY0%40momjian.us>
Precedence: bulk


--TmhkXvxWhTPx/HiP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Sep  6, 2023 at 08:01:30PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Tue, Jul 11, 2023 at 07:31:26AM -0700, David G. Johnston wrote:
> >> I think the complaint is that someone seeing the behavior in the wild comes to
> >> this order-of-operations and doesn't see that the observed behavior is
> >> documented.
> 
> > How is the attached patch?
> 
> Maybe better "Although query output columns are nominally computed in the
> next step, they can also be referenced (by name or by ordinal number)
> as <literal>GROUP BY</literal> elements".

I modified the text slightly in the attached patch.

> You could go further and add "Such references cannot be parts of
> <literal>GROUP BY</literal> expressions, however."  Not sure if we
> cover that explicitly anywhere else.

We do farther down the docs in the GROUP BY section:

	https://www.postgresql.org/docs/devel/sql-select.html

	GROUP BY will condense into a single row all selected rows that share
	the same values for the grouped expressions. An expression used inside a
	grouping_element can be an input column name, or the name or ordinal
	number of an output column (SELECT list item), or an arbitrary
	expression formed from input-column values. In case of ambiguity, a
	                  ------------------------
	GROUP BY name will be interpreted as an input-column name rather than an
	output column name.

It says

*  input column name
*  output column name or ordinal number
*  expression formed from input-column values

Is this too subtle?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

--TmhkXvxWhTPx/HiP
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="group-by.diff"

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 0ee0cc7e64..974153cb78 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -130,7 +130,10 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
       If the <literal>HAVING</literal> clause is present, it
       eliminates groups that do not satisfy the given condition.  (See
       <xref linkend="sql-groupby"/> and
-      <xref linkend="sql-having"/> below.)
+      <xref linkend="sql-having"/> below.)  
+      Although query output columns are nominally computed in the next
+      step, they can also be referenced (by name or ordinal number)
+      in the <literal>GROUP BY</literal> clause.
      </para>
     </listitem>
 

--TmhkXvxWhTPx/HiP--