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.
Date: Fri, 01 Nov 2024 13:12:03 -0400
Message-ID: <1730133.1730481123@sss.pgh.pa.us> (raw)
In-Reply-To: <18682-1d820e9e8f60c369@postgresql.org>
References: <18682-1d820e9e8f60c369@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, 459850212@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18682: Null grouping set with empty table returns a row contains null.
In-Reply-To: <1730133.1730481123@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox