A <grouping specification> of () (called grand total in the Standard) is equivalent to grouping the entire result Table;
If I get it correctly: select max(dummy) from dual where 0 = 1 group by grouping sets(()); and select max(dummy) from dual where 0 = 1 ; should have the same output. It's the case with PostgreSQL, not with Oracle. Hence it means it's PostgreSQL which conforms to the standard in this case. Regards, Phil ________________________________ De : Phil Florent <philflor...@hotmail.com> Envoyé : lundi 25 novembre 2019 22:18 À : Pavel Stehule <pavel.steh...@gmail.com> Cc : pgsql-hack...@postgresql.org <pgsql-hack...@postgresql.org> Objet : RE: GROUPING SETS and SQL standard Hi, Thank you, as you mentionned it's not really an interesting real life case anyway. Regards, Phil ________________________________ De : Pavel Stehule <pavel.steh...@gmail.com> Envoyé : lundi 25 novembre 2019 21:23 À : Phil Florent <philflor...@hotmail.com> Cc : pgsql-hack...@postgresql.org <pgsql-hack...@postgresql.org> Objet : Re: GROUPING SETS and SQL standard po 25. 11. 2019 v 20:32 odesílatel Phil Florent <philflor...@hotmail.com<mailto:philflor...@hotmail.com>> napsal: Hi, We are still on the process to migrate our applications from proprietary RDBMS to PostgreSQL. Here is a simple query executed on various systems (real query is different but this one does not need any data) : Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from (select 1 from dual where 0=1 group by grouping sets(())) tmp; COUNT(*) ---------- 0 select @@version; GO --------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) select count(*) from (select 1 as c1 where 0=1 group by grouping sets(())) tmp; GO ----------- 0 (1 rows affected) select version(); version ---------------------------------------------------------------------------------------------------------------- PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit select count(*) from (select 1 from dual where 0=1 group by grouping sets(())) tmp; count ------- 1 (1 ligne) 0 or 1, which behaviour conforms to the SQL standard ? We have a workaround and it's just informational. This example has not too much sense - I am not sure if these corner cases are described by ANSI SQL standards. If I add aggregate query to subquery - using grouping sets without aggregation function is strange, then Postgres result looks more correct postgres=# select 1, count(*) from dual group by grouping sets(()); ┌──────────┬───────┐ │ ?column? │ count │ ╞══════════╪═══════╡ │ 1 │ 1 │ └──────────┴───────┘ (1 row) postgres=# select 1, count(*) from dual where false group by grouping sets(()); ┌──────────┬───────┐ │ ?column? │ count │ ╞══════════╪═══════╡ │ 1 │ 0 │ └──────────┴───────┘ (1 row) SELECT count(*) from this should be one in both cases. I am not sure, if standard describe using grouping sets without any aggregation function Pavel Regards, Phil
