Re: Implementation of GROUPING SETS (T431: Extended grouping capabilities)

Here is.

I checked result with Oracle and basic results are same with one exception

this patch doesn't well do with expr specified sets

this result is correct

postgres=# select selling_date, baguette, canteen, sum(items),
grouping(baguette), grouping(selling_date),
grouping_id(baguette,selling_date) from baguette_selling group by
grouping sets(baguette, selling_date, canteen,());
selling_date | baguette | canteen | sum | grouping | grouping | grouping_id
--------------+----------+---------+-----+----------+----------+-------------
| golf | | 9 | 0 | 1 | 1
| buster | | 20 | 0 | 1 | 1
2007-10-30 | | | 17 | 1 | 0 | 2
2007-10-31 | | | 12 | 1 | 0 | 2
| | Prague | 14 | 1 | 1 | 3
| | Berlin | 15 | 1 | 1 | 3
| | | 29 | 1 | 1 | 3
(7 rows)

but this result not:

postgres=# select extract(day from selling_date), selling_date,
baguette, canteen, sum(items), grouping(baguette),
grouping(selling_date), grouping_id(baguette,selling_date) from
baguette_selling group by grouping sets(baguette, selling_date,
canteen, extract(day from selling_date))
;
date_part | selling_date | baguette | canteen | sum | grouping |
grouping | grouping_id
-----------+--------------+----------+---------+-----+----------+----------+-------------
| | golf | | 9 | 0 |
1 | 1
| | buster | | 20 | 0 |
1 | 1
30 | 2007-10-30 | | | 17 | 1 |
0 | 2
31 | 2007-10-31 | | | 12 | 1 |
0 | 2
| | | Prague | 14 | 1 |
1 | 3
| | | Berlin | 15 | 1 |
1 | 3
| | | | 29 | 1 |
1 | 3
(7 rows)

date_part column is problematic.

regards
Pavel Stehule

2009/5/13 David Fetter <david(at)fetter(dot)org>:
> On Wed, May 13, 2009 at 03:12:51PM +0200, Pavel Stehule wrote:
>> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
>> > On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote:
>> >> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
>> >> > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote:
>> >> >> this patch has some bugs but it is good prototype (it's more stable
>> >> >> than old patch):
>> >> >
>> >> > I'm not sure if you're at the point that you're interested in bug reports, but
>> >> > here's something that didn't behave as expected:
>> >> >
>> >> > 5432 josh(at)josh*# create table gsettest (prod_id integer, cust_id integer,
>> >> > quantity integer);
>> >> > CREATE TABLE
>> >> > 5432 josh(at)josh*# insert into gsettest select floor(random() * 10)::int,
>> >> > floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1,
>> >> > 100);
>> >> > INSERT 0 100
>> >> > 5432 josh(at)josh*# select prod_id, cust_id, sum(quantity) from gsettest group by
>> >> > cube (prod_id, cust_id) order by 1, 2;
>> >> >  prod_id | cust_id | sum
>> >> > ---------+---------+-----
>> >> >       5 |       7 |   4
>> >> >       8 |      16 |   3
>> >> >       9 |      19 |   8
>> >> >       4 |      13 |   3
>> >> >       8 |       8 |  15
>> >> >       5 |       2 |   4
>> >> >       7 |       6 |   7
>> >> >       6 |       6 |   3
>> >> > </snip>
>> >> >
>> >> > Note that the results aren't sorted. The following, though, works around it:
>> >>
>> >> I thing, so result should not be sorted - it's same like normal group by.
>> >
>> > Normal GROUP BY wouldn't have ignored the ORDER BY clause I included.
>>
>> sorry, now I understand - simply it is a bug. I fixed it
>
> Where's the new patch?
>
> Cheers,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>