From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | David Fetter <david(at)fetter(dot)org> |
Subject: | Re: Implementation of GROUPING SETS (T431: Extended grouping capabilities) |
Date: | 2009-05-13 19:30:20 |
Message-ID: | 162867790905131230g773781b4u32cf444149b5ceee@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
>
Attachment | Content-Type | Size |
---|---|---|
gsets-0.5.diff | text/x-patch | 50.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2009-05-13 19:33:25 | Re: New trigger option of pg_standby |
Previous Message | Simon Riggs | 2009-05-13 19:08:52 | Re: New trigger option of pg_standby |