Re: Implementation of GROUPING SETS (T431: Extended grouping capabilities) - Mailing list pgsql-hackers

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@fetter.org>:
> On Wed, May 13, 2009 at 03:12:51PM +0200, Pavel Stehule wrote:
>> 2009/5/13 Joshua Tolley <eggyknap@gmail.com>:
>> > On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote:
>> >> 2009/5/13 Joshua Tolley <eggyknap@gmail.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@josh*# create table gsettest (prod_id integer, cust_id integer,
>> >> > quantity integer);
>> >> > CREATE TABLE
>> >> > 5432 josh@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@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@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david.fetter@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>