Postgres Pro
Downloads
Home   >   mailing lists

RE: GROUPING SETS and SQL standard - Mailing list pgsql-hackers

From Phil Florent
Subject RE: GROUPING SETS and SQL standard
Date
Msg-id AM6PR02MB4519945006262E12A2D3B072BA4A0@AM6PR02MB4519.eurprd02.prod.outlook.com
Whole thread Raw
In response to RE: GROUPING SETS and SQL standard  (Phil Florent <philflorent@hotmail.com>)
Responses Re: GROUPING SETS and SQL standard  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Tree view

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> 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

pgsql-hackers by date:

Previous
From: Jeremy Schneider
Date:
Subject: Re: Proposal: Global Index
Next
From: Tom Lane
Date:
Subject: Re: GROUPING SETS and SQL standard