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@sss.pgh.pa.us>
Envoyé : mardi 26 novembre 2019 01:39
À : Phil Florent <philflorent@hotmail.com>
Cc : Pavel Stehule <pavel.stehule@gmail.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Objet : Re: GROUPING SETS and SQL standard
Phil Florent <philflorent@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