Re: Implementation of GROUPING SETS (T431: Extended grouping capabilities)
От | Pavel Stehule |
---|---|
Тема | Re: Implementation of GROUPING SETS (T431: Extended grouping capabilities) |
Дата | |
Msg-id | 162867790905131230g773781b4u32cf444149b5ceee@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Implementation of GROUPING SETS (T431: Extended grouping capabilities) (David Fetter <david@fetter.org>) |
Список | 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 >
Вложения
В списке pgsql-hackers по дате отправления: