RE: GROUPING SETS and SQL standard

Thank you, it's noticed. Seems Oracle does not like too much "grouping sets". We discovered we had more serious "wrong results" bugs with this clause in our migration process. Anyway we don't have to maintain a double compatibility and soon it won't be a problem anymore.
Regards
Phil

________________________________
De : Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Envoyé : mardi 26 novembre 2019 01:39
À : Phil Florent <philflorent(at)hotmail(dot)com>
Cc : Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>; pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)org>
Objet : 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