Thread: BUG #18682: Null grouping set with empty table returns a row contains null.

The following bug has been logged on the website:

Bug reference:      18682
Logged by:          Yue Xingzhi
Email address:      459850212@qq.com
PostgreSQL version: 15.0
Operating system:   CentOS
Description:

The test case is quite simple

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

Could you please explain the behavior or if it is buggy? Thx

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