Daniel Gustafsson <[email protected]> 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
