Re: GROUPING SETS and SQL standard

Phil Florent <philflorent(at)hotmail(dot)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