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
