Re: Final Patch for GROUPING SETS

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(at)tao11(dot)riddles(dot)org(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>