Thread
-
GROUPING SETS and SQL standard
Phil Florent <philflorent@hotmail.com> — 2019-11-25T19:31:42Z
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. Regards, Phil -
Re: GROUPING SETS and SQL standard
Pavel Stehule <pavel.stehule@gmail.com> — 2019-11-25T20:23:12Z
po 25. 11. 2019 v 20:32 odesílatel Phil Florent <philflorent@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 > >
-
RE: GROUPING SETS and SQL standard
Phil Florent <philflorent@hotmail.com> — 2019-11-25T21:18:00Z
Hi, Thank you, as you mentionned it's not really an interesting real life case anyway. Regards, Phil ________________________________ De : Pavel Stehule <pavel.stehule@gmail.com> Envoyé : lundi 25 novembre 2019 21:23 À : Phil Florent <philflorent@hotmail.com> Cc : pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org> Objet : Re: GROUPING SETS and SQL standard po 25. 11. 2019 v 20:32 odesílatel Phil Florent <philflorent@hotmail.com<mailto:philflorent@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 -
RE: GROUPING SETS and SQL standard
Phil Florent <philflorent@hotmail.com> — 2019-11-26T00:16:49Z
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 <philflorent@hotmail.com> Envoyé : lundi 25 novembre 2019 22:18 À : Pavel Stehule <pavel.stehule@gmail.com> Cc : pgsql-hackers@postgresql.org <pgsql-hackers@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.stehule@gmail.com> Envoyé : lundi 25 novembre 2019 21:23 À : Phil Florent <philflorent@hotmail.com> Cc : pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org> Objet : Re: GROUPING SETS and SQL standard po 25. 11. 2019 v 20:32 odesílatel Phil Florent <philflorent@hotmail.com<mailto:philflorent@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 -
Re: GROUPING SETS and SQL standard
Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-26T00:39:19Z
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 -
RE: GROUPING SETS and SQL standard
Phil Florent <philflorent@hotmail.com> — 2019-11-26T08:29:26Z
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