Home > mailing lists

Thread: GROUPING SETS and SQL standard

GROUPING SETS and SQL standard

From

Phil Florent

Date:

25 November 2019, 22:31:42

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(*)

----------

select @@version;

--------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Jul 12 2019 17:43:08

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;

-----------

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

From

Pavel Stehule

Date:

25 November 2019, 23:23:12

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

From

Phil Florent

Date:

26 November 2019, 00:18:00

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

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

From

Phil Florent

Date:

26 November 2019, 03:16:49

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

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

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

From

Tom Lane

Date:

26 November 2019, 03:39:19

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

From

Phil Florent

Date:

26 November 2019, 11:29:26

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