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(at)gmail(dot)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-----
>
>