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

Show quoted text

Regards,

Phil

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

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

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

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