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-bugs-owner+archive@lists.postgresql.org>)
	id 1t6vBy-00GRqn-Hl
	for pgsql-bugs@arkaria.postgresql.org; Fri, 01 Nov 2024 17:12:10 +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-bugs-owner+archive@lists.postgresql.org>)
	id 1t6vBw-00FbXD-Bb
	for pgsql-bugs@arkaria.postgresql.org; Fri, 01 Nov 2024 17:12:08 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1t6vBw-00FbTS-2F
	for pgsql-bugs@lists.postgresql.org; Fri, 01 Nov 2024 17:12:08 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by magus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1t6vBu-004DLz-6c
	for pgsql-bugs@lists.postgresql.org; Fri, 01 Nov 2024 17:12:07 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 4A1HC32A1730134;
	Fri, 1 Nov 2024 13:12:04 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: 459850212@qq.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18682: Null grouping set with empty table returns a row
 contains null.
In-reply-to: <18682-1d820e9e8f60c369@postgresql.org>
References: <18682-1d820e9e8f60c369@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 01 Nov 2024 04:07:14 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1730132.1730481123.1@sss.pgh.pa.us>
Date: Fri, 01 Nov 2024 13:12:03 -0400
Message-ID: <1730133.1730481123@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/1730133.1730481123%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> create table qt1 (c_bigint bigint,b  bit(1));
> SELECT avg(qt1.c_bigint) as c1 FROM qt1 GROUP BY grouping sets(()); --
> returns one row
> SELECT avg(qt1.c_bigint) as c1 FROM qt1 GROUP BY grouping sets((b)); --
> returns no rows

> As one would intuitively expect, empty table should return no rows for empty
> grouping set. Oracle also returns no rows

As far as I can see, we conform to the SQL standard and Oracle doesn't
(if it acts as you claim, which I didn't check).

The standard says (in SQL:2021, this is 7.13 <group by clause> general
rules 1 & 2):

    1) If no <where clause> is specified, then let T be the result of
    the preceding <from clause>; otherwise, let T be the result of the
    preceding <where clause>.

    2) Case:

      a) If there are no grouping columns, then the result of the
      <group by clause> is the grouped table consisting of T as its
      only group.

      b) Otherwise, the result of the <group by clause> is a
      partitioning of the rows of T into the minimum number of groups
      such that, for each grouping column of each group, no two values
      of that grouping column are distinct.

(This wording is identical as far back as SQL99, btw, so there's
nothing new here.)

Per rule 2a, "grouping sets(())" will result in a single group,
containing all the rows of T (even if that's zero rows).  This
is precisely analogous to what happens if you write an aggregate
call without any GROUP BY: you will get one row out no matter
what.

Per rule 2b, "grouping sets((b))" will result in one group for
each distinct value of "b" observed in the input; if the input
is empty then that's no groups.

			regards, tom lane