From: | Phil Florent <philflorent(at)hotmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE: GROUPING SETS and SQL standard |
Date: | 2019-11-26 00:16:49 |
Message-ID: | AM6PR02MB4519945006262E12A2D3B072BA4A0@AM6PR02MB4519.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)hotmail(dot)com>
Envoyé : lundi 25 novembre 2019 22:18
À : Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc : pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)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(dot)stehule(at)gmail(dot)com>
Envoyé : lundi 25 novembre 2019 21:23
À : Phil Florent <philflorent(at)hotmail(dot)com>
Cc : pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)org>
Objet : Re: GROUPING SETS and SQL standard
po 25. 11. 2019 v 20:32 odesílatel Phil Florent <philflorent(at)hotmail(dot)com<mailto:philflorent(at)hotmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-11-26 00:39:19 | Re: GROUPING SETS and SQL standard |
Previous Message | Jeremy Schneider | 2019-11-25 23:44:39 | Re: Proposal: Global Index |