Re: GROUPING SETS revisited

Hello

I am sending a updated version.

i hope so there is more comments, longer and more descriptive
identifiers and I fixed a few bugs. But I found some new bugs :(

What is ok:

create table cars(name varchar, place varchar, count integer);
insert into cars values('skoda', 'czech rep.', 10000);
insert into cars values('skoda', 'germany', 5000);
insert into cars values('bmw', 'czech rep.', 100);
insert into cars values('bmw', 'germany', 1000);
insert into cars values('opel', 'czech rep.', 7000);
insert into cars values('opel', 'germany', 7000);

postgres=# select name, place, sum(count) from cars group by ();
 name | place |  sum
------+-------+-------
      |       | 30100
(1 row)

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

postgres=# select name, place, sum(count) from cars group by grouping
sets(name, place),();
 name  |   place    |  sum
-------+------------+-------
 bmw   |            |  1100
 skoda |            | 15000
 opel  |            | 14000
       | germany    | 13000
       | czech rep. | 17100
       |            | 30100
(6 rows)

postgres=# select name, place, sum(count) from cars group by grouping
sets(name, place,()),();
 name  |   place    |  sum
-------+------------+-------
 bmw   |            |  1100
 skoda |            | 15000
 opel  |            | 14000
       | germany    | 13000
       | czech rep. | 17100
       |            | 30100
(6 rows)

postgres=# select name, place, sum(count), grouping(name) from cars
group by grouping sets(name);
 name  | place |  sum  | grouping
-------+-------+-------+----------
 bmw   |       |  1100 |        0
 skoda |       | 15000 |        0
 opel  |       | 14000 |        0
(3 rows)


what is wrong:

postgres=# select name, place from cars group by ();
 name  |   place
-------+------------
 skoda | czech rep.
 skoda | germany
 bmw   | czech rep.
 bmw   | germany
 opel  | czech rep.
 opel  | germany
(6 rows)

have to be NULL, NULL

postgres=# select name, place, sum(count), grouping(name) from cars
group by grouping sets(name) having grouping(name) = 1;
ERROR:  unrecognized node type: 934

my rewriting rule is applied too late and maybe isn't optimal. I
replace a grouping(x) by const. maybe is better to use a variable.
Same issue is with ORDER BY clause.

So Joshua, can you look on code?

Regards

Pavel Stehule


2010/8/5 Joshua Tolley <eggyknap@gmail.com>:
> On Thu, Aug 05, 2010 at 06:21:18AM +0200, Pavel Stehule wrote:
>> I hope, so next week you can do own work on this job - I am not a
>> native speaker, and my code will need a checking and fixing comments
>
> I haven't entirely figured out how the code in the old patch works, but I
> promise I *can* edit comments/docs :)
>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxaSjEACgkQRiRfCGf1UMM9dQCZASYJUmXLe5i7L4aQnMicwMfy
> cu8An3fMdR/ISezw5YV3KsCAOM+BILO1
> =uZb+
> -----END PGP SIGNATURE-----
>
>