From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Gustafsson <daniel@yesql.se>
Cc: Chao Li <li.evan.chao@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Fix GROUP BY ALL handling of ORDER BY operator semantics
Date: Tue, 14 Jul 2026 12:08:20 -0400
Message-ID: <3338663.1784045300@sss.pgh.pa.us> (raw)
In-Reply-To: <A3C289BE-8916-4DC6-962D-624F35E02B44@yesql.se>
References: <5243308F-8E5C-45AA-828C-FAD96C4F34DA@gmail.com>
<A0B7C417-5710-47B3-8FA2-C0DDA518DA07@yesql.se>
<3278800.1783989856@sss.pgh.pa.us>
<A3C289BE-8916-4DC6-962D-624F35E02B44@yesql.se>
Daniel Gustafsson <daniel@yesql.se> writes:
> Ack. Another case I am a bit confused by is:
> postgres=# create table t (a integer);
> CREATE TABLE
> postgres=# select 1 as x from t group by ();
> x
> ---
> 1
> (1 row)
> postgres=# select 1 as x from t group by all;
> x
> ---
> (0 rows)
> Shouldn't those two queries yield the same result, or am I missing something
> obvious?
I might be undercaffeinated still, but I think those are both correct.
"GROUP BY ()" has similar effects to use of an aggregate or HAVING
clause: it forces the table scan's results to be combined into a
single grouped row. But you get a grouped row even if the table is
empty. The second case is equivalent to "select 1 as x from t group
by x", and this is different because it will produce a grouped row
only if the table isn't empty. (The fact that the grouping expression
is a constant doesn't change the rule.) Not one of SQL's more
consistent behaviors perhaps, but I believe it's all per spec.
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-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, daniel@yesql.se, li.evan.chao@gmail.com
Subject: Re: Fix GROUP BY ALL handling of ORDER BY operator semantics
In-Reply-To: <3338663.1784045300@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