From: Tom Lane <tgl@sss.pgh.pa.us>
To: vectorplanck@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18758: Incorrect query result caused by ROLLUP operation
In-reply-to: <18758-7bbeea9719044a29@postgresql.org>
References: <18758-7bbeea9719044a29@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 30 Dec 2024 16:03:58 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3879323.1735585866.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 30 Dec 2024 14:11:06 -0500
Message-ID: <3879324.1735585866@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3879324.1735585866%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> create table t1 (c1 char(50));
> create table t2 (c1 integer);
> select  t1.c1 from t2, t1 group by rollup(t1.c1); -- actual: {null},
> expected: {}

This is the correct result AFAICS.  Per [1], ROLLUP(t1.c1) is a
shorthand for

GROUPING SETS (
    ( t1.c1 ),
    ( )
)

So first you get the output that would correspond to GROUP BY t1.c1,
and with no values of t1.c1 that output is indeed empty.  But then
you get the output for an empty grouping set, which is defined in the
same place as

    An empty grouping set means that all rows are aggregated down to a
    single group (which is output even if no input rows were present),
    as described above for the case of aggregate functions with no
    GROUP BY clause.

So a single row with a null value for t1.c1 is expected, even when
the FROM clause produces no rows.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/queries-table-expressions.html=
#QUERIES-GROUPING-SETS