Phil Florent <philflor...@hotmail.com> writes:
> A <grouping specification> of () (called grand total in the Standard) is
> equivalent to grouping the entire result Table;
Yeah, I believe so. Grouping by no columns is similar to what happens
if you compute an aggregate with no GROUP BY: the whole table is
taken as one group. If the table is empty, the group is empty, but
there's still a group --- that's why you get one aggregate output
value, not none, from
regression=# select count(*) from dual where 0 = 1;
count
-------
0
(1 row)
Thus, in your example, the sub-query should give
regression=# select 1 from dual where 0=1 group by grouping sets(());
?column?
----------
1
(1 row)
and therefore it's correct that
regression=# select count(*) from (select 1 from dual where 0=1 group by
grouping sets(())) tmp;
count
-------
1
(1 row)
AFAICS, Oracle and SQL Server are getting it wrong.
regards, tom lane
