Postgres Pro
Downloads
Home   >   mailing lists

Re: Final Patch for GROUPING SETS - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Final Patch for GROUPING SETS
Date
Msg-id CAFj8pRBUO78OcfRBLMkC_RUUzTa=pN49r8TMZinzV4QQM-gmAQ@mail.gmail.com
Whole thread Raw
In response to Re: Final Patch for GROUPING SETS  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Responses Re: Final Patch for GROUPING SETS
List pgsql-hackers
Tree view
Hi

I checked this patch, and it working very well

I found only two issue - I am not sure if it is issue

with data from https://wiki.postgresql.org/wiki/Grouping_Sets

postgres=# select name, place, sum(count), grouping(name), grouping(place) from cars group by rollup(name, place);
 name  |   place    |  sum  | grouping | grouping
-------+------------+-------+----------+----------
 bmw   | czech rep. |   100 |        0 |        0
 bmw   | germany    |  1000 |        0 |        0
 bmw   |            |  1100 |        0 |        1
 opel  | czech rep. |  7000 |        0 |        0
 opel  | germany    |  7000 |        0 |        0
 opel  |            | 14000 |        0 |        1
 skoda | czech rep. | 10000 |        0 |        0
 skoda | germany    |  5000 |        0 |        0
 skoda |            | 15000 |        0 |        1
       |            | 30100 |        1 |        1
(10 rows)

* redundant sets should be ignored

postgres=# select name, place, sum(count), grouping(name), grouping(place) from cars group by rollup(name, place), name;
 name  |   place    |  sum  | grouping | grouping
-------+------------+-------+----------+----------
 bmw   | czech rep. |   100 |        0 |        0
 bmw   | germany    |  1000 |        0 |        0
 bmw   |            |  1100 |        0 |        1
 bmw   |            |  1100 |        0 |        1
 opel  | czech rep. |  7000 |        0 |        0
 opel  | germany    |  7000 |        0 |        0
 opel  |            | 14000 |        0 |        1
 opel  |            | 14000 |        0 |        1
 skoda | czech rep. | 10000 |        0 |        0
 skoda | germany    |  5000 |        0 |        0
 skoda |            | 15000 |        0 |        1
 skoda |            | 15000 |        0 |        1
(12 rows)

It duplicate rows

postgres=# explain select name, place, sum(count), grouping(name), grouping(place) from cars group by rollup(name, place), name;
                               QUERY PLAN                              
------------------------------------------------------------------------
 GroupAggregate  (cost=10000000001.14..10000000001.38 rows=18 width=68)
   Grouping Sets: (name, place), (name), (name)
   ->  Sort  (cost=10000000001.14..10000000001.15 rows=6 width=68)
         Sort Key: name, place
         ->  Seq Scan on cars  (cost=0.00..1.06 rows=6 width=68)
 Planning time: 0.235 ms
(6 rows)

postgres=# select name, place, sum(count), grouping(name), grouping(place) from cars group by grouping sets((name, place), (name), (name),(place), ());
 name  |   place    |  sum  | grouping | grouping
-------+------------+-------+----------+----------
 bmw   | czech rep. |   100 |        0 |        0
 bmw   | germany    |  1000 |        0 |        0
 bmw   |            |  1100 |        0 |        1
 bmw   |            |  1100 |        0 |        1
 opel  | czech rep. |  7000 |        0 |        0
 opel  | germany    |  7000 |        0 |        0
 opel  |            | 14000 |        0 |        1
 opel  |            | 14000 |        0 |        1
 skoda | czech rep. | 10000 |        0 |        0
 skoda | germany    |  5000 |        0 |        0
 skoda |            | 15000 |        0 |        1
 skoda |            | 15000 |        0 |        1
       |            | 30100 |        1 |        1
       | czech rep. | 17100 |        1 |        0
       | germany    | 13000 |        1 |        0
(15 rows)

Fantastic work

Regards

Pavel




2014-08-25 7:21 GMT+02:00 Andrew Gierth <andrew@tao11.riddles.org.uk>:
Here is the new version of our grouping sets patch. This version
supersedes the previous post.

We believe the functionality of this version to be substantially
complete, providing all the standard grouping set features except T434
(GROUP BY DISTINCT).  (Additional tweaks, such as extra variants on
GROUPING(), could be added for compatibility with other databases.)

Since the debate regarding reserved keywords has not produced any
useful answer, the main patch here makes CUBE and ROLLUP into
col_name_reserved keywords, but a separate small patch is attached to
make them unreserved_keywords instead.

So there are now 5 files:

gsp1.patch         - phase 1 code patch (full syntax, limited functionality)
gsp2.patch         - phase 2 code patch (adds full functionality using the
                     new chained aggregate mechanism)
gsp-doc.patch      - docs
gsp-contrib.patch  - quote "cube" in contrib/cube and contrib/earthdistance,
                     intended primarily for testing pending a decision on
                     renaming contrib/cube or unreserving keywords
gsp-u.patch        - proposed method to unreserve CUBE and ROLLUP

--
Andrew (irc:RhodiumToad)



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Concurrently option for reindexdb
Next
From: Bruce Momjian
Date:
Subject: Re: Is this a bug?