pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #18682: Null grouping set with empty table returns a row contains null. 2+ messages / 2 participants [nested] [flat]
* BUG #18682: Null grouping set with empty table returns a row contains null. @ 2024-11-01 04:07 PG Bug reporting form <noreply@postgresql.org> 2024-11-01 17:12 ` Re: BUG #18682: Null grouping set with empty table returns a row contains null. Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: PG Bug reporting form @ 2024-11-01 04:07 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: 459850212@qq.com 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18682: Null grouping set with empty table returns a row contains null. 2024-11-01 04:07 BUG #18682: Null grouping set with empty table returns a row contains null. PG Bug reporting form <noreply@postgresql.org> @ 2024-11-01 17:12 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2024-11-01 17:12 UTC (permalink / raw) To: 459850212@qq.com; +Cc: pgsql-bugs@lists.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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-01 17:12 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-11-01 04:07 BUG #18682: Null grouping set with empty table returns a row contains null. PG Bug reporting form <noreply@postgresql.org> 2024-11-01 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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