Re: GROUPING SETS and SQL standard

po 25. 11. 2019 v 20:32 odesílatel Phil Florent <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
>
>