Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-26 Thread Andres Freund 
On 2015-07-17 19:57:22 +0100, Andrew Gierth wrote:
> Attached is the current version of my fix (with Jeevan's regression
> tests plus one of mine).

Pushed, thanks for the report and fix!


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-26 Thread Andres Freund 
On 2015-07-17 11:37:26 +0530, Jeevan Chalke wrote:
> However I wonder why we are supporting GROUPING SETS inside GROUPING SETS.
> On Oracle, it is throwing an error.
> We are not trying to be Oracle compatible, but just curious to know.

The SQL specification seems to be pretty unambigous about supporting
nested grouping set specifications. Check 7.9 :  (nested inside a )
can contain .

Regards,

Andres


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-21 Thread Kyotaro HORIGUCHI 
Hello,

At Mon, 20 Jul 2015 15:45:21 +0530, Jeevan Chalke 
 wrote in 

> On Sat, Jul 18, 2015 at 12:27 AM, Andrew Gierth  > wrote:
> 
> > > "Kyotaro" == Kyotaro HORIGUCHI 
> > writes:
> >
> >  Kyotaro> Hello, this looks to be a kind of thinko. The attached patch
> >  Kyotaro> fixes it.
> >
> > No, that's still wrong. Just knowing that there is a List is not enough
> > to tell whether to concat it or append it.

Thank you. I've missed the non-grouping-set cases.

> > Jeevan's original patch tries to get around this by making the RowExpr
> > case wrap another List around its result (which is then removed by the
> > concat), but this is the wrong approach too because it breaks nested
> > RowExprs (which isn't valid syntax in the spec, because the spec allows
> > only column references in GROUP BY, not arbitrary expressions, but which
> > we have no reason not to support).
> 
> > Attached is the current version of my fix (with Jeevan's regression
> > tests plus one of mine).
> >
> 
> Looks good to me.

It also looks for me to work as expected and to be in good shape.

The two foreach loops for T_GroupingSet and T_List became to look
very simiar but they don't seem can be merged in reasonable
shape.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-20 Thread Jeevan Chalke 
On Sat, Jul 18, 2015 at 12:27 AM, Andrew Gierth  wrote:

> > "Kyotaro" == Kyotaro HORIGUCHI 
> writes:
>
>  Kyotaro> Hello, this looks to be a kind of thinko. The attached patch
>  Kyotaro> fixes it.
>
> No, that's still wrong. Just knowing that there is a List is not enough
> to tell whether to concat it or append it.
>
> Jeevan's original patch tries to get around this by making the RowExpr
> case wrap another List around its result (which is then removed by the
> concat), but this is the wrong approach too because it breaks nested
> RowExprs (which isn't valid syntax in the spec, because the spec allows
> only column references in GROUP BY, not arbitrary expressions, but which
> we have no reason not to support).
>
> Attached is the current version of my fix (with Jeevan's regression
> tests plus one of mine).
>

Looks good to me.


>
> --
> Andrew (irc:RhodiumToad)
>
>


-- 
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-17 Thread Andrew Gierth 
> "Kyotaro" == Kyotaro HORIGUCHI  writes:

 Kyotaro> Hello, this looks to be a kind of thinko. The attached patch
 Kyotaro> fixes it.

No, that's still wrong. Just knowing that there is a List is not enough
to tell whether to concat it or append it.

Jeevan's original patch tries to get around this by making the RowExpr
case wrap another List around its result (which is then removed by the
concat), but this is the wrong approach too because it breaks nested
RowExprs (which isn't valid syntax in the spec, because the spec allows
only column references in GROUP BY, not arbitrary expressions, but which
we have no reason not to support).

Attached is the current version of my fix (with Jeevan's regression
tests plus one of mine).

-- 
Andrew (irc:RhodiumToad)

diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index e90e1d6..5a48a02 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1734,7 +1734,7 @@ findTargetlistEntrySQL99(ParseState *pstate, Node *node, List **tlist,
  * Inside a grouping set (ROLLUP, CUBE, or GROUPING SETS), we expect the
  * content to be nested no more than 2 deep: i.e. ROLLUP((a,b),(c,d)) is
  * ok, but ROLLUP((a,(b,c)),d) is flattened to ((a,b,c),d), which we then
- * normalize to ((a,b,c),(d)).
+ * (later) normalize to ((a,b,c),(d)).
  *
  * CUBE or ROLLUP can be nested inside GROUPING SETS (but not the reverse),
  * and we leave that alone if we find it. But if we see GROUPING SETS inside
@@ -1803,9 +1803,16 @@ flatten_grouping_sets(Node *expr, bool toplevel, bool *hasGroupingSets)
 
 foreach(l2, gset->content)
 {
-	Node	   *n2 = flatten_grouping_sets(lfirst(l2), false, NULL);
+	Node	   *n1 = lfirst(l2);
+	Node	   *n2 = flatten_grouping_sets(n1, false, NULL);
 
-	result_set = lappend(result_set, n2);
+	if (IsA(n1, GroupingSet) &&
+		((GroupingSet *)n1)->kind == GROUPING_SET_SETS)
+	{
+		result_set = list_concat(result_set, (List *) n2);
+	}
+	else
+		result_set = lappend(result_set, n2);
 }
 
 /*
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 842c2ae..ff3ba9b 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -145,6 +145,127 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
|   |  12 |   36
 (6 rows)
 
+-- nesting with grouping sets
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets((), grouping sets((
+  order by 1 desc;
+ sum 
+-
+  12
+  12
+  12
+(3 rows)
+
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets((), grouping sets(((a, b)
+  order by 1 desc;
+ sum 
+-
+  12
+  12
+   8
+   2
+   2
+(5 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c
+  order by 1 desc;
+ sum 
+-
+  12
+  12
+   6
+   6
+   6
+   6
+(6 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(a, grouping sets(a, cube(b)))
+  order by 1 desc;
+ sum 
+-
+  12
+  10
+  10
+   8
+   4
+   2
+   2
+(7 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets((a, (b
+  order by 1 desc;
+ sum 
+-
+   8
+   2
+   2
+(3 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets((a, b)))
+  order by 1 desc;
+ sum 
+-
+   8
+   2
+   2
+(3 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(a, grouping sets(a), a))
+  order by 1 desc;
+ sum 
+-
+  10
+  10
+  10
+   2
+   2
+   2
+(6 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a))
+  order by 1 desc;
+ sum 
+-
+  10
+  10
+  10
+  10
+  10
+  10
+  10
+  10
+   2
+   2
+   2
+   2
+   2
+   2
+   2
+   2
+(16 rows)
+
+select sum(c) from gstest2
+  group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a))
+  order by 1 desc;
+ sum 
+-
+  10
+   8
+   8
+   2
+   2
+   2
+   2
+   2
+(8 rows)
+
 -- empty input: first is 0 rows, second 1, third 3 etc.
 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
  a | b | sum | count 
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 0bffb85..d886fae 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -73,6 +73,35 @@ select grouping(a), a, array_agg(b),
 select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
   from gstest2 group by rollup (a,b) order by rsum, a, b;
 
+-- nesting with grouping sets
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets((), grouping sets((
+  order by 1 desc;
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets((), grouping sets(((a, b)
+  order by 1 desc;
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(rollup(c), groupi

Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-17 Thread Kyotaro HORIGUCHI 
Hello, this looks to be a kind of thinko. The attached patch
fixes it.

===
According to the comment of transformGroupingSet, it assumes that
the given GROUPING SETS node is already flatted out and
flatten_grouping_sets() does that. The details of the
transformation is described in the comment for the function.

The problmen is what does the function for nested grouping sets.

> Node *n2 = flatten_grouping_sets(lfirst(l2), false, NULL);
> result_set = lappend(result_set, n2);

This does not flattens the list as required. n2 should be
concatenated if it is a list. The attached small patch fixes it
and the problematic query returns sane (perhaps) result.

# Though I don't know the exact definition of the syntax..

=# select sum(c) from gstest2 group by grouping sets ((), grouping sets ((), 
grouping sets ((;
 sum 
-
  12
  12
  12
(3 rows)

=# select sum(c) from gstest2 group by grouping sets ((a), grouping sets ((b), 
grouping sets ((c;
 sum 
-
  10
   2
   6
   6
   8
   4
(6 rows)

regards,

At Fri, 17 Jul 2015 11:37:26 +0530, Jeevan Chalke 
 wrote in 

> >  Jeevan> It looks like we do support nested GROUPING SETS, I mean Sets
> >  Jeevan> withing Sets, not other types.  However this nesting is broken.
> >
> > Good catch, but I'm not yet sure your fix is correct; I'll need to look
> > into that.
> >
> 
> Sure. Thanks.
> 
> However I wonder why we are supporting GROUPING SETS inside GROUPING SETS.
> On Oracle, it is throwing an error.
> We are not trying to be Oracle compatible, but just curious to know.
> 
> I have tried restricting it in attached patch.
> 
> But it may require few comment adjustment.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index e90e1d6..708ebc9 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1804,8 +1804,10 @@ flatten_grouping_sets(Node *expr, bool toplevel, bool *hasGroupingSets)
 foreach(l2, gset->content)
 {
 	Node	   *n2 = flatten_grouping_sets(lfirst(l2), false, NULL);
-
-	result_set = lappend(result_set, n2);
+	if (IsA(n2, List))
+		result_set = list_concat(result_set, (List *)n2);
+	else
+		result_set = lappend(result_set, n2);
 }
 
 /*

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-16 Thread Jeevan Chalke 
On Wed, Jul 15, 2015 at 10:21 PM, Andrew Gierth  wrote:

> > "Jeevan" == Jeevan Chalke  writes:
>
>  Jeevan> Hi,
>  Jeevan> It looks like we do support nested GROUPING SETS, I mean Sets
>  Jeevan> withing Sets, not other types.  However this nesting is broken.
>
> Good catch, but I'm not yet sure your fix is correct; I'll need to look
> into that.
>

Sure. Thanks.

However I wonder why we are supporting GROUPING SETS inside GROUPING SETS.
On Oracle, it is throwing an error.
We are not trying to be Oracle compatible, but just curious to know.

I have tried restricting it in attached patch.

But it may require few comment adjustment.

Thanks

>
> --
> Andrew (irc:RhodiumToad)
>



-- 
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e0ff6f1..738715f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -371,9 +371,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 relation_expr_list dostmt_opt_list
 transform_element_list transform_type_list
 
-%type 	group_by_list
+%type 	group_by_list grouping_sets_list
 %type 	group_by_item empty_grouping_set rollup_clause cube_clause
-%type 	grouping_sets_clause
+%type 	grouping_sets_clause grouping_sets_item
 
 %type 	opt_fdw_options fdw_options
 %type 	fdw_option
@@ -10343,6 +10343,18 @@ group_by_item:
 			| grouping_sets_clause	{ $$ = $1; }
 		;
 
+grouping_sets_list:
+			grouping_sets_item{ $$ = list_make1($1); }
+			| grouping_sets_list ',' grouping_sets_item		{ $$ = lappend($1,$3); }
+		;
+
+grouping_sets_item:
+			a_expr	{ $$ = $1; }
+			| empty_grouping_set	{ $$ = $1; }
+			| cube_clause			{ $$ = $1; }
+			| rollup_clause			{ $$ = $1; }
+		;
+
 empty_grouping_set:
 			'(' ')'
 {
@@ -10371,7 +10383,7 @@ cube_clause:
 		;
 
 grouping_sets_clause:
-			GROUPING SETS '(' group_by_list ')'
+			GROUPING SETS '(' grouping_sets_list ')'
 {
 	$$ = (Node *) makeGroupingSet(GROUPING_SET_SETS, $4, @1);
 }
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 842c2ae..e75dceb 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -145,6 +145,25 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
|   |  12 |   36
 (6 rows)
 
+-- nesting with grouping sets
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets(((a, b
+  order by 1 desc;
+ERROR:  syntax error at or near "sets"
+LINE 2:   group by grouping sets((), grouping sets(((a, b
+  ^
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c
+  order by 1 desc;
+ERROR:  syntax error at or near "sets"
+LINE 2:   group by grouping sets(grouping sets(rollup(c), grouping s...
+  ^
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(a, grouping sets(a), a))
+  order by 1 desc;
+ERROR:  syntax error at or near "sets"
+LINE 2:   group by grouping sets(grouping sets(a, grouping sets(a), ...
+  ^
 -- empty input: first is 0 rows, second 1, third 3 etc.
 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
  a | b | sum | count 
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 0bffb85..b7e4826 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -73,6 +73,17 @@ select grouping(a), a, array_agg(b),
 select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
   from gstest2 group by rollup (a,b) order by rsum, a, b;
 
+-- nesting with grouping sets
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets(((a, b
+  order by 1 desc;
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c
+  order by 1 desc;
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(a, grouping sets(a), a))
+  order by 1 desc;
+
 -- empty input: first is 0 rows, second 1, third 3 etc.
 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),());

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-15 Thread Andrew Gierth 
> "Jeevan" == Jeevan Chalke  writes:

 Jeevan> Hi,
 Jeevan> It looks like we do support nested GROUPING SETS, I mean Sets
 Jeevan> withing Sets, not other types.  However this nesting is broken.

Good catch, but I'm not yet sure your fix is correct; I'll need to look
into that.

-- 
Andrew (irc:RhodiumToad)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Grouping Sets: Fix unrecognized node type bug

2015-07-15 Thread Jeevan Chalke 
Hi,

It looks like we do support nested GROUPING SETS, I mean Sets withing
Sets, not other types.  However this nesting is broken.

Here is the simple example where I would expect three rows in the
result.  But unfortunately it is giving "unrecognized node type"
error.  Which is something weird and need a fix.

postgres=# create table gstest2 (a integer, b integer, c integer);
postgres=# insert into gstest2 values (1,1,1), (1,1,1), (1,1,1),
(1,1,1), (1,1,1), (1,1,1), (1,1,2), (1,2,2), (2,2,2);
postgres=# select sum(c) from gstest2
  group by grouping sets((), grouping sets((), grouping sets((
  order by 1 desc;
ERROR:  unrecognized node type: 926


I spend much time to understand the cause and was looking into
transformGroupingSet() and transformGroupClauseList() function.
I have tried fixing "unrecognized node type: 926" error there,
but later it is failing with "unrecognized node type: 656".

Later I have realized that we have actually have an issue while
flattening grouping sets.  If we have nested grouping sets like
above, then we are getting GroupingSet node inside the list and
transformGroupClauseList() does not expect that and end up with
this error.

I have tried fixing this issue in flatten_grouping_sets(), after
flattening grouping sets node, we need to concat the result with
the existing list and should not append.  This alone does not
solve the issue as we need a list when we have ROW expression.
Thus there, if not top level, I am creating a list now.

Attached patch with few testcases too.

Please have a look.

Thanks

-- 
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index e90e1d6..31d4331 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1779,8 +1779,19 @@ flatten_grouping_sets(Node *expr, bool toplevel, bool *hasGroupingSets)
 RowExpr*r = (RowExpr *) expr;
 
 if (r->row_format == COERCE_IMPLICIT_CAST)
-	return flatten_grouping_sets((Node *) r->args,
- false, NULL);
+{
+	Node   *n1 = flatten_grouping_sets((Node *) r->args,
+	   false, NULL);
+
+	/*
+	 * Make a list for row expression if toplevel is false,
+	 * return flatten list otherwise
+	 */
+	if (toplevel)
+		return (Node *) n1;
+	else
+		return (Node *) list_make1(n1);
+}
 			}
 			break;
 		case T_GroupingSet:
@@ -1805,7 +1816,10 @@ flatten_grouping_sets(Node *expr, bool toplevel, bool *hasGroupingSets)
 {
 	Node	   *n2 = flatten_grouping_sets(lfirst(l2), false, NULL);
 
-	result_set = lappend(result_set, n2);
+	if (IsA(n2, List))
+		result_set = list_concat(result_set, (List *) n2);
+	else
+		result_set = lappend(result_set, n2);
 }
 
 /*
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index adb39b3..5c47717 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -145,6 +145,112 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
|   |  12 |   36
 (6 rows)
 
+-- nesting with grouping sets
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets((), grouping sets((
+  order by 1 desc;
+ sum 
+-
+  12
+  12
+  12
+(3 rows)
+
+select sum(c) from gstest2
+  group by grouping sets((), grouping sets((), grouping sets(((a, b)
+  order by 1 desc;
+ sum 
+-
+  12
+  12
+   8
+   2
+   2
+(5 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c
+  order by 1 desc;
+ sum 
+-
+  12
+  12
+   6
+   6
+   6
+   6
+(6 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(a, grouping sets(a, cube(b)))
+  order by 1 desc;
+ sum 
+-
+  12
+  10
+  10
+   8
+   4
+   2
+   2
+(7 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets((a, (b
+  order by 1 desc;
+ sum 
+-
+   8
+   2
+   2
+(3 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets((a, b)))
+  order by 1 desc;
+ sum 
+-
+   8
+   2
+   2
+(3 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(a, grouping sets(a), a))
+  order by 1 desc;
+ sum 
+-
+  10
+  10
+  10
+   2
+   2
+   2
+(6 rows)
+
+select sum(c) from gstest2
+  group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a))
+  order by 1 desc;
+ sum 
+-
+  10
+  10
+  10
+  10
+  10
+  10
+  10
+  10
+   2
+   2
+   2
+   2
+   2
+   2
+   2
+   2
+(16 rows)
+
 -- empty input: first is 0 rows, second 1, third 3 etc.
 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
  a | b | sum | count 
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 0883afd..e478d34 100644
--- a/src/test

Re: [HACKERS] Grouping Sets

2013-07-04 Thread Dev Kumkar 
On Thu, Jul 4, 2013 at 7:53 PM, Atri Sharma  wrote:

> On Thu, Jul 4, 2013 at 6:56 PM, Dev Kumkar 
> wrote:
> > Ok, no problem. Will await for any other pointers regarding any related
> > patch here.
> >
> > Currently using UNION to archive similar results but looking if anything
> is
> > already done here.
> >
> > Looks like GROUPING SET was in the TODO list long back.
> >
> http://grokbase.com/t/postgresql/pgsql-general/06aaa4g7cq/cube-rollup-grouping-sets
> >
> > Am I missing anything here?
> >
> > Regards...
>
> Me and RhodiumToad discussed the idea recently, after David Fetter
> suggested that we work on it. We may start work on it soon, haven't
> thought in detail yet though.


Ok, 9.3 feature wise looks all done.

So I believe it will be in any 9.3 + release?

Till then will continue UNION approach as looks like it gives the necessary
functionality. Any loopholes here friends?

Regards...

Re: [HACKERS] Grouping Sets

2013-07-04 Thread Atri Sharma 
On Thu, Jul 4, 2013 at 6:56 PM, Dev Kumkar  wrote:
> On Thu, Jul 4, 2013 at 6:31 PM, Pavel Stehule 
> wrote:
>>
>> Hello
>>
>>
>> I don't work on this topic now, and my code is not usable for production.
>
>
> Ok, no problem. Will await for any other pointers regarding any related
> patch here.
>
> Currently using UNION to archive similar results but looking if anything is
> already done here.
>
> Looks like GROUPING SET was in the TODO list long back.
> http://grokbase.com/t/postgresql/pgsql-general/06aaa4g7cq/cube-rollup-grouping-sets
>
> Am I missing anything here?
>
> Regards...

Me and RhodiumToad discussed the idea recently, after David Fetter
suggested that we work on it. We may start work on it soon, haven't
thought in detail yet though.
--
Regards,

Atri
l'apprenant


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets

2013-07-04 Thread Dev Kumkar 
On Thu, Jul 4, 2013 at 6:31 PM, Pavel Stehule wrote:

> Hello
>
> I don't work on this topic now, and my code is not usable for production.


Ok, no problem. Will await for any other pointers regarding any related
patch here.

Currently using UNION to archive similar results but looking if anything is
already done here.

Looks like GROUPING SET was in the TODO list long back.
http://grokbase.com/t/postgresql/pgsql-general/06aaa4g7cq/cube-rollup-grouping-sets

Am I missing anything here?

Regards...

Re: [HACKERS] Grouping Sets

2013-07-04 Thread Pavel Stehule 
Hello

2013/7/4 Dev Kumkar :
> Hello,
>
> Am looking for the patch related to 'Implementation of GROUPING SETS'.
> Where can get this from?
>
> Related thread:
> http://www.postgresql.org/message-id/162867790905121420p7c910054x24d8e327abd58...@mail.gmail.com
>

I don't work on this topic now, and my code is not usable for production.

Regards

Pavel Stehule

> Regards...


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Grouping Sets

2013-07-04 Thread Dev Kumkar 
 Hello,

Am looking for the patch related to 'Implementation of GROUPING SETS'.
Where can get this from?

Related thread:
http://www.postgresql.org/message-id/162867790905121420p7c910054x24d8e327abd58...@mail.gmail.com

Regards...

Re: [HACKERS] Grouping Sets

2011-09-20 Thread Pavel Stehule 
Hello

2011/9/20 David Rinaldi :
> Since it seems that you have spent some considerable time investigating and
> producing a working concept, what would your best guess time estimate be,
> assuming the requisite skills/talent/will in (planner/executor/etc.), to
> have a solid working module put together? Are we looking at something like
> 40 hours or more like 5000 hours, in your estimate?
>

it depends on your knowledge of pg internals and your motivation :). I
thing so it can be less than 40 hours for elimination of these issues
and next 40 hours for some finalisation.

If I remember well, I had a prototype after one week of hacking, and I
am not a strong programmer.

Regards

Pavel Stehule

> Thanks.
>
> --
> Regards
>
> David
>
> -Original Message-
> From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
> Sent: Monday, September 19, 2011 10:45 PM
> To: edwbro...@gmail.com
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Grouping Sets
>
> Hello
>
> 2011/9/20 David Rinaldi :
>> Paul,
>>
>> I was able to apply the patch to 9.0.4 and so far looks good.  My Oracle
>> results match. Nice.
>>
>> But, when trying to calculate some percentages and control some rounding,
>> the results are coming back as null for some reason.  I have tried
> casting,
>> to_char, etc to try to get them to show up..no love ensued. I was
> wondering
>> if you have any idea what could by happening. I have attached some test
>> results based on the grouping sets wiki. One of the examples is just using
>> group by, as a sanity check.  Any ideas or help would be much appreciated.
>>
>
> sorry, I have not any useful idea. This work was a concept and it is
> probable, so there will be some corner issues :(.
>
> This feature needs more love and some more significant changes in
> planner and executor.
>
> Regards
>
> Pavel
>
>
>>
>> CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
>> sales real, cost real );
>>
>> insert into cars2 values('skoda', 'czech rep.', 1, 8000);
>> insert into cars2 values('skoda', 'germany', 5000, 6000);
>> insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
>> insert into cars2 values('bmw', 'germany', 18000, 15000);
>> insert into cars2 values('opel', 'czech rep.', 7000, 5000);
>> insert into cars2 values('opel', 'germany', 7000, 5000);
>>
>> --grouping sets test--
>>
>> select name, place,
>> sum(sales) as sales,
>> sum(cost) as cost,
>> sum(cost) / sum(sales) as cost_sales_ratio,
>> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
>> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
>> from cars2 group by rollup(name, place);
>>
>>
>> name    place   sales   cost    cost_sales_ratio
>  cost_sales_ratio_per
>> cost_sales_ratio_per_rnd
>> bmw     czech rep.      6000    4000    0.6667  (null)  (null)
>> skoda   germany 5000    6000    1.2     (null)  (null)
>> opel    czech rep.      7000    5000    0.7143  (null)  (null)
>> opel    germany 7000    5000    0.7143  (null)  (null)
>> skoda   czech rep.      1   8000    0.8     (null)  (null)
>> bmw     germany 18000   15000   0.8333  (null)  (null)
>> bmw     (null)  24000   19000   0.7917  (null)  (null)
>> skoda   (null)  15000   14000   0.9333  (null)  (null)
>> opel    (null)  14000   1   0.7143  (null)  (null)
>> (null)  (null)  53000   43000   0.8113  (null)  (null)
>>
>> --group by sanity test--
>>
>> select name, place,
>> sum(sales) as sales,
>> sum(cost) as cost,
>> sum(cost) / sum(sales) as cost_sales_ratio,
>> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
>> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
>> from cars2 group by name, place;
>>
>> name    place   sales   cost    cost_sales_ratio
>  cost_sales_ratio_per
>> cost_sales_ratio_per_rnd
>> bmw     czech rep.      6000    4000    0.6667  66.6667 67
>> skoda   germany 5000    6000    1.2     120     120
>> opel    czech rep.      7000    5000    0.7143  71.4286 71
>> opel    germany 7000    5000    0.7143  71.4286 71
>> skoda   czech rep.      1   8000    0.8     80      80
>> bmw     germany 18000   15000   0.8333  83. 83
>>
>>
>>
>> Thanks
>>
>>
>>
>>
>> --
>> Regards
>>
>> David
>>
>>
>> -Original Message-
>> From: Pavel Stehule [mailto:pavel

Re: [HACKERS] Grouping Sets

2011-09-20 Thread David Rinaldi 
Since it seems that you have spent some considerable time investigating and
producing a working concept, what would your best guess time estimate be,
assuming the requisite skills/talent/will in (planner/executor/etc.), to
have a solid working module put together? Are we looking at something like
40 hours or more like 5000 hours, in your estimate? 

Thanks. 

--
Regards

David 

-Original Message-
From: Pavel Stehule [mailto:pavel.steh...@gmail.com] 
Sent: Monday, September 19, 2011 10:45 PM
To: edwbro...@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Grouping Sets

Hello

2011/9/20 David Rinaldi :
> Paul,
>
> I was able to apply the patch to 9.0.4 and so far looks good.  My Oracle
> results match. Nice.
>
> But, when trying to calculate some percentages and control some rounding,
> the results are coming back as null for some reason.  I have tried
casting,
> to_char, etc to try to get them to show up..no love ensued. I was
wondering
> if you have any idea what could by happening. I have attached some test
> results based on the grouping sets wiki. One of the examples is just using
> group by, as a sanity check.  Any ideas or help would be much appreciated.
>

sorry, I have not any useful idea. This work was a concept and it is
probable, so there will be some corner issues :(.

This feature needs more love and some more significant changes in
planner and executor.

Regards

Pavel


>
> CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
> sales real, cost real );
>
> insert into cars2 values('skoda', 'czech rep.', 1, 8000);
> insert into cars2 values('skoda', 'germany', 5000, 6000);
> insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
> insert into cars2 values('bmw', 'germany', 18000, 15000);
> insert into cars2 values('opel', 'czech rep.', 7000, 5000);
> insert into cars2 values('opel', 'germany', 7000, 5000);
>
> --grouping sets test--
>
> select name, place,
> sum(sales) as sales,
> sum(cost) as cost,
> sum(cost) / sum(sales) as cost_sales_ratio,
> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
> from cars2 group by rollup(name, place);
>
>
> name    place   sales   cost    cost_sales_ratio      
 cost_sales_ratio_per
> cost_sales_ratio_per_rnd
> bmw     czech rep.      6000    4000    0.6667  (null)  (null)
> skoda   germany 5000    6000    1.2     (null)  (null)
> opel    czech rep.      7000    5000    0.7143  (null)  (null)
> opel    germany 7000    5000    0.7143  (null)  (null)
> skoda   czech rep.      1   8000    0.8     (null)  (null)
> bmw     germany 18000   15000   0.8333  (null)  (null)
> bmw     (null)  24000   19000   0.7917  (null)  (null)
> skoda   (null)  15000   14000   0.9333  (null)  (null)
> opel    (null)  14000   1   0.7143  (null)  (null)
> (null)  (null)  53000   43000   0.8113  (null)  (null)
>
> --group by sanity test--
>
> select name, place,
> sum(sales) as sales,
> sum(cost) as cost,
> sum(cost) / sum(sales) as cost_sales_ratio,
> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
> from cars2 group by name, place;
>
> name    place   sales   cost    cost_sales_ratio      
 cost_sales_ratio_per
> cost_sales_ratio_per_rnd
> bmw     czech rep.      6000    4000    0.6667  66.6667 67
> skoda   germany 5000    6000    1.2     120     120
> opel    czech rep.      7000    5000    0.7143  71.4286 71
> opel    germany 7000    5000    0.7143  71.4286 71
> skoda   czech rep.      1   8000    0.8     80      80
> bmw     germany 18000   15000   0.8333  83. 83
>
>
>
> Thanks
>
>
>
>
> --
> Regards
>
> David
>
>
> -Original Message-
> From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
> Sent: Sunday, September 18, 2011 2:34 PM
> To: David Rinaldi
> Subject: Re: [HACKERS] Grouping Sets
>
> Hello
>
> A last patch should be applied on 8.4 or 9.0 - should to try it. I
> worked with developer version.
>
> http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php
>
> Regards
>
> Pavel Stehule
>
> 2011/9/18 David Rinaldi :
>> Hi,
>>
>> I tried to apply the Grouping Sets Patch to 8.4, but received several
> Hunks
>> failed messages, does anyone know if the failing hunks can be applied
>> manually?  Or what version they were applied to specifically?
>>
>> --
>> Regards
>>
>> David
>>
>
>


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets

2011-09-19 Thread Pavel Stehule 
Hello

2011/9/20 David Rinaldi :
> Paul,
>
> I was able to apply the patch to 9.0.4 and so far looks good.  My Oracle
> results match. Nice.
>
> But, when trying to calculate some percentages and control some rounding,
> the results are coming back as null for some reason.  I have tried casting,
> to_char, etc to try to get them to show up..no love ensued. I was wondering
> if you have any idea what could by happening. I have attached some test
> results based on the grouping sets wiki. One of the examples is just using
> group by, as a sanity check.  Any ideas or help would be much appreciated.
>

sorry, I have not any useful idea. This work was a concept and it is
probable, so there will be some corner issues :(.

This feature needs more love and some more significant changes in
planner and executor.

Regards

Pavel


>
> CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
> sales real, cost real );
>
> insert into cars2 values('skoda', 'czech rep.', 1, 8000);
> insert into cars2 values('skoda', 'germany', 5000, 6000);
> insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
> insert into cars2 values('bmw', 'germany', 18000, 15000);
> insert into cars2 values('opel', 'czech rep.', 7000, 5000);
> insert into cars2 values('opel', 'germany', 7000, 5000);
>
> --grouping sets test--
>
> select name, place,
> sum(sales) as sales,
> sum(cost) as cost,
> sum(cost) / sum(sales) as cost_sales_ratio,
> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
> from cars2 group by rollup(name, place);
>
>
> name    place   sales   cost    cost_sales_ratio        cost_sales_ratio_per
> cost_sales_ratio_per_rnd
> bmw     czech rep.      6000    4000    0.6667  (null)  (null)
> skoda   germany 5000    6000    1.2     (null)  (null)
> opel    czech rep.      7000    5000    0.7143  (null)  (null)
> opel    germany 7000    5000    0.7143  (null)  (null)
> skoda   czech rep.      1   8000    0.8     (null)  (null)
> bmw     germany 18000   15000   0.8333  (null)  (null)
> bmw     (null)  24000   19000   0.7917  (null)  (null)
> skoda   (null)  15000   14000   0.9333  (null)  (null)
> opel    (null)  14000   1   0.7143  (null)  (null)
> (null)  (null)  53000   43000   0.8113  (null)  (null)
>
> --group by sanity test--
>
> select name, place,
> sum(sales) as sales,
> sum(cost) as cost,
> sum(cost) / sum(sales) as cost_sales_ratio,
> (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
> round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
> from cars2 group by name, place;
>
> name    place   sales   cost    cost_sales_ratio        cost_sales_ratio_per
> cost_sales_ratio_per_rnd
> bmw     czech rep.      6000    4000    0.6667  66.6667 67
> skoda   germany 5000    6000    1.2     120     120
> opel    czech rep.      7000    5000    0.7143  71.4286 71
> opel    germany 7000    5000    0.7143  71.4286 71
> skoda   czech rep.      1   8000    0.8     80      80
> bmw     germany 18000   15000   0.8333  83. 83
>
>
>
> Thanks
>
>
>
>
> --
> Regards
>
> David
>
>
> -Original Message-
> From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
> Sent: Sunday, September 18, 2011 2:34 PM
> To: David Rinaldi
> Subject: Re: [HACKERS] Grouping Sets
>
> Hello
>
> A last patch should be applied on 8.4 or 9.0 - should to try it. I
> worked with developer version.
>
> http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php
>
> Regards
>
> Pavel Stehule
>
> 2011/9/18 David Rinaldi :
>> Hi,
>>
>> I tried to apply the Grouping Sets Patch to 8.4, but received several
> Hunks
>> failed messages, does anyone know if the failing hunks can be applied
>> manually?  Or what version they were applied to specifically?
>>
>> --
>> Regards
>>
>> David
>>
>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets

2011-09-19 Thread David Rinaldi 
Paul,

I was able to apply the patch to 9.0.4 and so far looks good.  My Oracle
results match. Nice.

But, when trying to calculate some percentages and control some rounding,
the results are coming back as null for some reason.  I have tried casting,
to_char, etc to try to get them to show up..no love ensued. I was wondering
if you have any idea what could by happening. I have attached some test
results based on the grouping sets wiki. One of the examples is just using
group by, as a sanity check.  Any ideas or help would be much appreciated.


CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
sales real, cost real );

insert into cars2 values('skoda', 'czech rep.', 1, 8000);
insert into cars2 values('skoda', 'germany', 5000, 6000);
insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
insert into cars2 values('bmw', 'germany', 18000, 15000);
insert into cars2 values('opel', 'czech rep.', 7000, 5000);
insert into cars2 values('opel', 'germany', 7000, 5000);

--grouping sets test--

select name, place, 
sum(sales) as sales, 
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by rollup(name, place);


nameplace   sales   costcost_sales_ratiocost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep.  600040000.6667  (null)  (null)
skoda   germany 500060001.2 (null)  (null)
opelczech rep.  700050000.7143  (null)  (null)
opelgermany 700050000.7143  (null)  (null)
skoda   czech rep.  1   80000.8 (null)  (null)
bmw germany 18000   15000   0.8333  (null)  (null)
bmw (null)  24000   19000   0.7917  (null)  (null)
skoda   (null)  15000   14000   0.9333  (null)  (null)
opel(null)  14000   1   0.7143  (null)  (null)
(null)  (null)  53000   43000   0.8113  (null)  (null)

--group by sanity test--

select name, place, 
sum(sales) as sales, 
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by name, place;

nameplace   sales   costcost_sales_ratiocost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep.  600040000.6667  66.6667 67
skoda   germany 500060001.2 120 120
opelczech rep.  700050000.7143  71.4286 71
opelgermany 700050000.7143  71.4286 71
skoda   czech rep.  1   80000.8 80  80
bmw germany 18000   15000   0.8333  83. 83



Thanks 




--
Regards

David
 

-Original Message-
From: Pavel Stehule [mailto:pavel.steh...@gmail.com] 
Sent: Sunday, September 18, 2011 2:34 PM
To: David Rinaldi
Subject: Re: [HACKERS] Grouping Sets

Hello

A last patch should be applied on 8.4 or 9.0 - should to try it. I
worked with developer version.

http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php

Regards

Pavel Stehule

2011/9/18 David Rinaldi :
> Hi,
>
> I tried to apply the Grouping Sets Patch to 8.4, but received several
Hunks
> failed messages, does anyone know if the failing hunks can be applied
> manually?  Or what version they were applied to specifically?
>
> --
> Regards
>
> David
>


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Grouping Sets

2011-09-19 Thread Joshua Tolley 
On Sun, Sep 18, 2011 at 02:08:01PM -0500, David Rinaldi wrote:
> I tried to apply the Grouping Sets Patch to 8.4, but received several Hunks
> failed messages, does anyone know if the failing hunks can be applied
> manually?  Or what version they were applied to specifically?

Your best bet is probably to get the code from approximately the date of the
patch. As far as I know it hasn't been touched in a while, and didn't work
well back when it was being actively developed.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


pgpeMKakIJ2SX.pgp
Description: PGP signature

[HACKERS] Grouping Sets

2011-09-18 Thread David Rinaldi 
Hi,

I tried to apply the Grouping Sets Patch to 8.4, but received several Hunks
failed messages, does anyone know if the failing hunks can be applied
manually?  Or what version they were applied to specifically?

-- 
Regards

David

Re: [HACKERS] Grouping Sets

2011-06-20 Thread Pavel Stehule 
Hello

I have not any newest patch related to GROUPING SETS. The last version
of this patch is probably correct, but it is not well tested.
Actually, this patch has not quality to production usage :(. It is
just concept. You can test it.

Regards

Pavel Stehule

2011/6/18 Mariano Mara :
> Hi hackers (and specially Pavel Stehule),
>  I could really use the grouping set feature for some complex queries I'm
>  migrating from other db vendor. If my WEB searching is precise, this
>  wiki page [1] and this thread[2] are the last updates on the subject.
>  I'm willing to test how these functions in my project but some questions 
> first:
>  1- is there an up-to-date version of the patch that I should be aware of?
>  2- Can I apply that patch to 8.4.8?
>  3- any extra recommendations?
>
> TIA,
> Mariano
>
> [1] http://wiki.postgresql.org/wiki/Grouping_Sets
> [2] http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Grouping Sets

2011-06-18 Thread Mariano Mara 
Hi hackers (and specially Pavel Stehule),
 I could really use the grouping set feature for some complex queries I'm
 migrating from other db vendor. If my WEB searching is precise, this
 wiki page [1] and this thread[2] are the last updates on the subject. 
 I'm willing to test how these functions in my project but some questions first:
  1- is there an up-to-date version of the patch that I should be aware of?
  2- Can I apply that patch to 8.4.8?
  3- any extra recommendations?  

TIA,
Mariano

[1] http://wiki.postgresql.org/wiki/Grouping_Sets
[2] http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-18 Thread Pavel Stehule 
Hello

I found a break in GROUPING SETS implementation. Now I am playing with
own executor and planner node and I can't to go forward :(. Probably
this feature will need a significant update of our agg implementation.
Probably needs a some similar structure like CTE but it can be a
little bit reduced - there are a simple relation between source query
and result query - I am not sure, if this has to be implemented via
subqueries? The second question is relative big differencies between
GROUP BY behave and GROUP BY GROUPING SETS behave. Now I don't know
about way to join GROUP BY and GROUPING SETS together

Any ideas welcome

Regards

Pavel

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] grouping sets - updated patch

2010-08-09 Thread Pavel Stehule 
2010/8/10 Joshua Tolley :
> On Mon, Aug 09, 2010 at 10:59:26PM +0200, Pavel Stehule wrote:
>> Hello
>>
>> I fixed an issues with empty sets. It just work, but there are some ugly 
>> hacks.
>>
>> It's really needs own planner node - now grouping functions are not
>> supported by ORDER BY clause.
>
> I haven't made it through the last version much, but I'll poke through this
> instead. I have a few days of family business coming up, and might be
> unrespondive during that time.
>

ok,

Pavel
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxgxlgACgkQRiRfCGf1UMM3NwCgkJ3EEWIj6MLiDcU2SHT/hH7a
> 4BsAn2hTqqzsLYLFZbflIJK/x/WMsZ2d
> =dIod
> -END PGP SIGNATURE-
>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] grouping sets - updated patch

2010-08-09 Thread Joshua Tolley 
On Mon, Aug 09, 2010 at 10:59:26PM +0200, Pavel Stehule wrote:
> Hello
> 
> I fixed an issues with empty sets. It just work, but there are some ugly 
> hacks.
> 
> It's really needs own planner node - now grouping functions are not
> supported by ORDER BY clause.

I haven't made it through the last version much, but I'll poke through this
instead. I have a few days of family business coming up, and might be
unrespondive during that time.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-09 Thread Pavel Stehule 
Hello

I was confused when I though so I found a solution of 1 shift/reduce conflict :(

All identificators used for buildin functions have to be a
col_name_keywords or reserved keyword. There is conflict with our
(probably obsolete) feature SELECT colname(tabname). So for this
moment the real solution is removing CUBE and ROLLUP from keywords and
dynamically testing a funcname in transformation stage - what is
slower and more ugly.

ideas?

Regards

Pavel Stehule


2010/8/7 Pavel Stehule :
> 2010/8/7 Joshua Tolley :
>> On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote:
>>> I am sending a updated version.
>>
>> I've been looking at the changes to gram.y, and noted the comment under 
>> func_expr
>> where you added CUBE and ROLLUP definitions. It says that CUBE can't be a
>> reserved keyword because it's already used in the cube contrib module. But
>> then the changes to kwlist.h include this:
>>
>
> I am little bit confused now - it's bad comment - and I have to verify
> it. What I remember, we cannot to use a two parser's rules, because it
> going to a conflict. So there have to be used a trick with a moving to
> decision to transform stage, where we have a context info. I have to
> recheck a minimal level - probably it can't be a RESERVED_KEYWORD.
> Because then we can't to create a function "cube".
>
>> + PG_KEYWORD("cube", CUBE, RESERVED_KEYWORD)
>> ...
>> + PG_KEYWORD("rollup", ROLLUP, RESERVED_KEYWORD)
>>
>> ...and CUBE and ROLLUP are added in gram.y under the reserved_keyword list. I
>> realize things like CURRENT_TIME, that also have special entries in the
>> func_expr grammar, are also reserved keywords, but this all seems at odds 
>> with
>> the comment. What am I missing? Is the comment simply pointing out that the
>> designation of CUBE and ROLLUP as reserved keywords will have to change at
>> some point, but it hasn't been implemented yet (or no one has figured out how
>> to do it)?
>>
>> --
>> Joshua Tolley / eggyknap
>> End Point Corporation
>> http://www.endpoint.com
>>
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.9 (GNU/Linux)
>>
>> iEYEARECAAYFAkxcjSIACgkQRiRfCGf1UMPpCwCcCHBh/1NiLykIcVYgPyfbIegF
>> xq0AoID75rCPiW8yf29OSkaJVza1FQt5
>> =PcLs
>> -END PGP SIGNATURE-
>>
>>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-06 Thread Pavel Stehule 
2010/8/7 Joshua Tolley :
> On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote:
>> I am sending a updated version.
>
> I've been looking at the changes to gram.y, and noted the comment under 
> func_expr
> where you added CUBE and ROLLUP definitions. It says that CUBE can't be a
> reserved keyword because it's already used in the cube contrib module. But
> then the changes to kwlist.h include this:
>

I am little bit confused now - it's bad comment - and I have to verify
it. What I remember, we cannot to use a two parser's rules, because it
going to a conflict. So there have to be used a trick with a moving to
decision to transform stage, where we have a context info. I have to
recheck a minimal level - probably it can't be a RESERVED_KEYWORD.
Because then we can't to create a function "cube".

> + PG_KEYWORD("cube", CUBE, RESERVED_KEYWORD)
> ...
> + PG_KEYWORD("rollup", ROLLUP, RESERVED_KEYWORD)
>
> ...and CUBE and ROLLUP are added in gram.y under the reserved_keyword list. I
> realize things like CURRENT_TIME, that also have special entries in the
> func_expr grammar, are also reserved keywords, but this all seems at odds with
> the comment. What am I missing? Is the comment simply pointing out that the
> designation of CUBE and ROLLUP as reserved keywords will have to change at
> some point, but it hasn't been implemented yet (or no one has figured out how
> to do it)?
>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxcjSIACgkQRiRfCGf1UMPpCwCcCHBh/1NiLykIcVYgPyfbIegF
> xq0AoID75rCPiW8yf29OSkaJVza1FQt5
> =PcLs
> -END PGP SIGNATURE-
>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-06 Thread Joshua Tolley 
On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote:
> I am sending a updated version.

I've been looking at the changes to gram.y, and noted the comment under 
func_expr
where you added CUBE and ROLLUP definitions. It says that CUBE can't be a
reserved keyword because it's already used in the cube contrib module. But
then the changes to kwlist.h include this:

+ PG_KEYWORD("cube", CUBE, RESERVED_KEYWORD)
...
+ PG_KEYWORD("rollup", ROLLUP, RESERVED_KEYWORD)

...and CUBE and ROLLUP are added in gram.y under the reserved_keyword list. I
realize things like CURRENT_TIME, that also have special entries in the
func_expr grammar, are also reserved keywords, but this all seems at odds with
the comment. What am I missing? Is the comment simply pointing out that the
designation of CUBE and ROLLUP as reserved keywords will have to change at
some point, but it hasn't been implemented yet (or no one has figured out how
to do it)?

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-05 Thread Pavel Stehule 
I found other issue :(

postgres=#  select name, place from cars group by grouping sets(name, place,());
 name  |   place
---+
 bmw   |
 skoda |
 opel  |
   | germany
   | czech rep.
 skoda | czech rep.
 skoda | germany
 bmw   | czech rep.
 bmw   | germany
 opel  | czech rep.
 opel  | germany
(11 rows)

postgres=# explain select name, place from cars group by grouping
sets(name, place,());
  QUERY PLAN
--
 Append  (cost=36.98..88.55 rows=1230 width=54)
   CTE GroupingSets
 ->  Seq Scan on cars  (cost=0.00..18.30 rows=830 width=68)
   ->  HashAggregate  (cost=18.68..20.68 rows=200 width=32)
 ->  CTE Scan on "GroupingSets"  (cost=0.00..16.60 rows=830 width=32)
   ->  HashAggregate  (cost=18.68..20.68 rows=200 width=32)
 ->  CTE Scan on "GroupingSets"  (cost=0.00..16.60 rows=830 width=32)
   ->  CTE Scan on "GroupingSets"  (cost=0.00..16.60 rows=830 width=64)
(8 rows)

the combination of nonagregates and empty sets do a problems - because
we can't ensure agg mode without aggregates or group by. But it is
only minor issue

2010/8/5 Joshua Tolley :
> On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote:
>> So Joshua, can you look on code?
>
> Sure... thanks :)
>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxa1NsACgkQRiRfCGf1UMPwzQCgjz52P86Yx4ac4aRkKwjn8OHK
> 6/EAoJ/CjXEyPaLpx39SI5bKQPz+AwBR
> =Mi2J
> -END PGP SIGNATURE-
>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-05 Thread Joshua Tolley 
On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote:
> So Joshua, can you look on code?

Sure... thanks :)

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-04 Thread Joshua Tolley 
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


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-04 Thread Pavel Stehule 
2010/8/4 Joshua Tolley :
> On Wed, Aug 04, 2010 at 04:44:05AM +0200, Pavel Stehule wrote:
>> > Yeah, I seem to have done a poor job of producing the patch based on the
>> > repository I was working from. That said, it seems Pavel's working 
>> > actively on
>> > a patch anyway, so perhaps my updating the old one isn't all that 
>> > worthwhile.
>> > Pavel, is your code somewhere that we can get to it?
>> >
>>
>> not now. please wait a week.
>
> That works for me. I'm glad to try doing a better job of putting together my
> version of the patch, if anyone thinks it's useful, but it seems that since
> Pavel's code is due to appear sometime in the foreseeable future, there's not
> much point in my doing that.
>

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

Regards

Pavel

> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxZkp8ACgkQRiRfCGf1UMMUcwCfcPayQbWRUYwhpCF1f24LsdD9
> H/gAnRzCEq6yLX/RVLLi88ROhurOzbhK
> =gUPx
> -END PGP SIGNATURE-
>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-04 Thread Joshua Tolley 
On Wed, Aug 04, 2010 at 04:44:05AM +0200, Pavel Stehule wrote:
> > Yeah, I seem to have done a poor job of producing the patch based on the
> > repository I was working from. That said, it seems Pavel's working actively 
> > on
> > a patch anyway, so perhaps my updating the old one isn't all that 
> > worthwhile.
> > Pavel, is your code somewhere that we can get to it?
> >
> 
> not now. please wait a week.

That works for me. I'm glad to try doing a better job of putting together my
version of the patch, if anyone thinks it's useful, but it seems that since
Pavel's code is due to appear sometime in the foreseeable future, there's not
much point in my doing that.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Pavel Stehule 
2010/8/3 Joshua Tolley :
> On Tue, Aug 03, 2010 at 12:58:03PM -0700, David Fetter wrote:
>> On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote:
>> > In case anyone's interested, I've taken the CTE-based grouping sets
>> > patch from [1] and made it apply to 9.1, attached. I haven't yet
>> > done things like checked it for whitespace consistency, style
>> > conformity, or anything else, but (tuits permitting) hope to figure
>> > out how it works and get it closer to commitability in some upcoming
>> > commitfest.
>> >
>> > I mention it here so that if someone else is working on it, we can
>> > avoid duplicated effort, and to see if a CTE-based grouping sets
>> > implementation is really the way we think we want to go.
>> >
>> > [1]
>> > http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php
>>
>> I've added back one file in the patch enclosed here.  I'm still
>> getting compile fails from
>>
>> CC="ccache gcc" ./configure     --prefix=$PG_PREFIX     
>> --with-pgport=$PGPORT     --with-perl     --with-libxml     --enable-debug   
>>   --enable-cassert
>> make
>>
>> Log from that also enclosed.
>>
>
> Yeah, I seem to have done a poor job of producing the patch based on the
> repository I was working from. That said, it seems Pavel's working actively on
> a patch anyway, so perhaps my updating the old one isn't all that worthwhile.
> Pavel, is your code somewhere that we can get to it?
>

not now. please wait a week.

Regards

Pavel

> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxYeiQACgkQRiRfCGf1UMPlEQCff+I4sCGtR+lzUs6Wb5JKi7Uu
> 3qYAnjLHzHzyMSHHX55QsphkaBbEJ0Zf
> =uRqV
> -END PGP SIGNATURE-
>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Joshua Tolley 
On Tue, Aug 03, 2010 at 12:58:03PM -0700, David Fetter wrote:
> On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote:
> > In case anyone's interested, I've taken the CTE-based grouping sets
> > patch from [1] and made it apply to 9.1, attached. I haven't yet
> > done things like checked it for whitespace consistency, style
> > conformity, or anything else, but (tuits permitting) hope to figure
> > out how it works and get it closer to commitability in some upcoming
> > commitfest.
> > 
> > I mention it here so that if someone else is working on it, we can
> > avoid duplicated effort, and to see if a CTE-based grouping sets
> > implementation is really the way we think we want to go.
> > 
> > [1]
> > http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php
> 
> I've added back one file in the patch enclosed here.  I'm still
> getting compile fails from
> 
> CC="ccache gcc" ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT 
> --with-perl --with-libxml --enable-debug --enable-cassert
> make
> 
> Log from that also enclosed.
> 

Yeah, I seem to have done a poor job of producing the patch based on the
repository I was working from. That said, it seems Pavel's working actively on
a patch anyway, so perhaps my updating the old one isn't all that worthwhile.
Pavel, is your code somewhere that we can get to it?

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread David Fetter 
On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote:
> In case anyone's interested, I've taken the CTE-based grouping sets
> patch from [1] and made it apply to 9.1, attached. I haven't yet
> done things like checked it for whitespace consistency, style
> conformity, or anything else, but (tuits permitting) hope to figure
> out how it works and get it closer to commitability in some upcoming
> commitfest.
> 
> I mention it here so that if someone else is working on it, we can
> avoid duplicated effort, and to see if a CTE-based grouping sets
> implementation is really the way we think we want to go.
> 
> [1]
> http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php

I've added back one file in the patch enclosed here.  I'm still
getting compile fails from

CC="ccache gcc" ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT   
  --with-perl --with-libxml --enable-debug --enable-cassert
make

Log from that also enclosed.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/src/backend/parser/Makefile b/src/backend/parser/Makefile
index a8f4c07..fb248a6 100644
--- a/src/backend/parser/Makefile
+++ b/src/backend/parser/Makefile
@@ -15,7 +15,7 @@ override CPPFLAGS := -I. -I$(srcdir) $(CPPFLAGS)
 OBJS= analyze.o gram.o keywords.o kwlookup.o parser.o \
   parse_agg.o parse_clause.o parse_coerce.o parse_cte.o parse_expr.o \
   parse_func.o parse_node.o parse_oper.o parse_param.o parse_relation.o \
-  parse_target.o parse_type.o parse_utilcmd.o scansup.o
+  parse_target.o parse_type.o parse_utilcmd.o scansup.o parse_gsets.o
 
 FLEXFLAGS = -CF
 
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6b99a10..1b579a8 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -34,6 +34,7 @@
 #include "parser/parse_clause.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_cte.h"
+#include "parser/parse_gsets.h"
 #include "parser/parse_oper.h"
 #include "parser/parse_param.h"
 #include "parser/parse_relation.h"
@@ -150,6 +151,163 @@ parse_sub_analyze(Node *parseTree, ParseState 
*parentParseState,
 }
 
 /*
+ * process GROUPING SETS
+ */
+static SelectStmt *
+makeSelectStmt(List *targetList, List *fromClause)
+{
+   SelectStmt *n = makeNode(SelectStmt);
+   n->distinctClause = NULL;
+   n->intoClause = NULL;
+   n->targetList = targetList;
+   n->fromClause = fromClause;
+   n->whereClause = NULL;
+   n->groupClause = NULL;
+   n->havingClause = NULL;
+   n->windowClause = NIL;
+   n->withClause = NULL;
+   n->valuesLists = NIL;
+   n->sortClause = NIL;
+   n->limitOffset = NULL;
+   n->limitCount = NULL;
+   n->lockingClause = NIL;
+   n->op = SETOP_NONE;
+   n->all = false;
+   n->larg = NULL;
+   n->rarg = NULL;
+   return n;
+}
+
+static List *
+makeStarTargetList(void)
+{
+   ResTarget *rt = makeNode(ResTarget);
+   
+   rt->name = NULL;
+   rt->indirection = NIL;
+   rt->val = (Node *) makeNode(ColumnRef);
+   ((ColumnRef *) rt->val)->fields = list_make1(makeNode(A_Star));
+   rt->location = -1;
+
+   return list_make1(rt);
+}
+ 
+static SelectStmt *
+transformGroupingSets(ParseState *pstate, SelectStmt *stmt)
+{
+   if (stmt->groupClause && IsA(stmt->groupClause, GroupByClause))
+   {
+   GroupingSetsSpec *gss = (GroupingSetsSpec *) 
expandGroupingSets(pstate, 
+   (List *)((GroupByClause 
*)stmt->groupClause)->fields);
+   
+   if (pstate->p_hasGroupingSets)
+   {
+   CommonTableExpr *cte = makeNode(CommonTableExpr);
+   SelectStmt  *cteedstmt;
+   int ngroupingsets = list_length(gss->set_list) + 
(gss->has_empty_set ? 1 : 0);
+   boolall = ((GroupByClause *) 
stmt->groupClause)->all;
+   
+   cteedstmt = makeSelectStmt(NIL, NIL);
+   cteedstmt->intoClause = stmt->intoClause;
+   cteedstmt->sortClause = stmt->sortClause;
+   cteedstmt->limitOffset = stmt->limitOffset;
+   cteedstmt->limitCount = stmt->limitCount;
+   cteedstmt->lockingClause = stmt->lockingClause;
+   
+   cte->ctename = "**g**";
+   cte->ctequery = (Node *) stmt;
+   cte->location = -1;
+   
+   cteedstmt->withClause = makeNode(WithClause);
+   cteedstmt->withClause->ctes = list_make1(cte);
+   cteedstmt->w

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Pavel Stehule 
2010/8/3 Hitoshi Harada :
> 2010/8/3 Pavel Stehule :
>> Hello
>>
>> 2010/8/3 Joshua Tolley :
>>> In case anyone's interested, I've taken the CTE-based grouping sets patch 
>>> from
>>> [1] and made it apply to 9.1, attached. I haven't yet done things like 
>>> checked
>>> it for whitespace consistency, style conformity, or anything else, but 
>>> (tuits
>>> permitting) hope to figure out how it works and get it closer to 
>>> commitability
>>> in some upcoming commitfest.
>>>
>>> I mention it here so that if someone else is working on it, we can avoid
>>> duplicated effort, and to see if a CTE-based grouping sets implementation is
>>> really the way we think we want to go.
>>>
>>
>> I am plaing with it now :). I have a plan to replace CTE with similar
>> but explicit executor node. The main issue of existing patch was using
>> just transformation to CTE. I agree, so it isn't too much extensiable
>> in future. Now I am cleaning identifiers little bit. Any colaboration
>> is welcome.
>>
>> My plan:
>> 1) clean CTE patch
>> 2) replace CTE with explicit executor node, but still based on tuplestore
>> 3) when will be possible parallel processing based on hash agg - then
>> we don't need to use tuplestore
>
> Couldn't you explain what exactly "explicit executor node"? I hope we
> can share your image to develop it further than only transformation to
> CTE.

I have a one reason

Implementation based on CTE doesn't create space for possible
optimalisations (I think now, maybe it isn't true). It is good for
initial or referencial implementation - but it can be too complex,
when we will try to append some optimalizations - like parallel hash
agg processing, direct data reading without tuplestore. If you are, as
CTE author, thinking so these features are possible in non recursive
CTE too, I am not agains. I hope so this week I'll have a CTE based
patch - and we can talk about next direction. I see as possible
performance issue using a tuplestore - there are lot of cases where
repeating of source query can be faster.

If I remember well, Tom has a objection, so transformation to CTE is
too early - in parser. So It will be first change. Executor node can
be CTE.

regards

Pavel

p.s. I am sure, so there are lot of task, that can be solved together
with non recursive CTE.

>
>
> Regards,
>
> --
> Hitoshi Harada
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Hitoshi Harada 
2010/8/3 Pavel Stehule :
> Hello
>
> 2010/8/3 Joshua Tolley :
>> In case anyone's interested, I've taken the CTE-based grouping sets patch 
>> from
>> [1] and made it apply to 9.1, attached. I haven't yet done things like 
>> checked
>> it for whitespace consistency, style conformity, or anything else, but (tuits
>> permitting) hope to figure out how it works and get it closer to 
>> commitability
>> in some upcoming commitfest.
>>
>> I mention it here so that if someone else is working on it, we can avoid
>> duplicated effort, and to see if a CTE-based grouping sets implementation is
>> really the way we think we want to go.
>>
>
> I am plaing with it now :). I have a plan to replace CTE with similar
> but explicit executor node. The main issue of existing patch was using
> just transformation to CTE. I agree, so it isn't too much extensiable
> in future. Now I am cleaning identifiers little bit. Any colaboration
> is welcome.
>
> My plan:
> 1) clean CTE patch
> 2) replace CTE with explicit executor node, but still based on tuplestore
> 3) when will be possible parallel processing based on hash agg - then
> we don't need to use tuplestore

Couldn't you explain what exactly "explicit executor node"? I hope we
can share your image to develop it further than only transformation to
CTE.


Regards,

-- 
Hitoshi Harada

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GROUPING SETS revisited

2010-08-02 Thread Pavel Stehule 
Hello

2010/8/3 Joshua Tolley :
> In case anyone's interested, I've taken the CTE-based grouping sets patch from
> [1] and made it apply to 9.1, attached. I haven't yet done things like checked
> it for whitespace consistency, style conformity, or anything else, but (tuits
> permitting) hope to figure out how it works and get it closer to commitability
> in some upcoming commitfest.
>
> I mention it here so that if someone else is working on it, we can avoid
> duplicated effort, and to see if a CTE-based grouping sets implementation is
> really the way we think we want to go.
>

I am plaing with it now :). I have a plan to replace CTE with similar
but explicit executor node. The main issue of existing patch was using
just transformation to CTE. I agree, so it isn't too much extensiable
in future. Now I am cleaning identifiers little bit. Any colaboration
is welcome.

My plan:
1) clean CTE patch
2) replace CTE with explicit executor node, but still based on tuplestore
3) when will be possible parallel processing based on hash agg - then
we don't need to use tuplestore

comments??

Regards

Pavel

> [1] http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php
>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxXrggACgkQRiRfCGf1UMMlCQCglaIdtPj8Qe6G60V2LHn5pFNn
> kgIAniXRgIQEbVrK/eDVZnmKCzw33lT9
> =XVVV
> -END PGP SIGNATURE-
>
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] GROUPING SETS revisited

2010-08-02 Thread Joshua Tolley 
In case anyone's interested, I've taken the CTE-based grouping sets patch from
[1] and made it apply to 9.1, attached. I haven't yet done things like checked
it for whitespace consistency, style conformity, or anything else, but (tuits
permitting) hope to figure out how it works and get it closer to commitability
in some upcoming commitfest.

I mention it here so that if someone else is working on it, we can avoid
duplicated effort, and to see if a CTE-based grouping sets implementation is
really the way we think we want to go.

[1] http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
diff --git a/src/backend/parser/Makefile b/src/backend/parser/Makefile
index a8f4c07..fb248a6 100644
*** a/src/backend/parser/Makefile
--- b/src/backend/parser/Makefile
*** override CPPFLAGS := -I. -I$(srcdir) $(C
*** 15,21 
  OBJS= analyze.o gram.o keywords.o kwlookup.o parser.o \
parse_agg.o parse_clause.o parse_coerce.o parse_cte.o parse_expr.o \
parse_func.o parse_node.o parse_oper.o parse_param.o parse_relation.o \
!   parse_target.o parse_type.o parse_utilcmd.o scansup.o
  
  FLEXFLAGS = -CF
  
--- 15,21 
  OBJS= analyze.o gram.o keywords.o kwlookup.o parser.o \
parse_agg.o parse_clause.o parse_coerce.o parse_cte.o parse_expr.o \
parse_func.o parse_node.o parse_oper.o parse_param.o parse_relation.o \
!   parse_target.o parse_type.o parse_utilcmd.o scansup.o parse_gsets.o
  
  FLEXFLAGS = -CF
  
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6b99a10..1b579a8 100644
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***
*** 34,39 
--- 34,40 
  #include "parser/parse_clause.h"
  #include "parser/parse_coerce.h"
  #include "parser/parse_cte.h"
+ #include "parser/parse_gsets.h"
  #include "parser/parse_oper.h"
  #include "parser/parse_param.h"
  #include "parser/parse_relation.h"
*** parse_sub_analyze(Node *parseTree, Parse
*** 150,155 
--- 151,313 
  }
  
  /*
+  * process GROUPING SETS
+  */
+ static SelectStmt *
+ makeSelectStmt(List *targetList, List *fromClause)
+ {
+ 	SelectStmt *n = makeNode(SelectStmt);
+ 	n->distinctClause = NULL;
+ 	n->intoClause = NULL;
+ 	n->targetList = targetList;
+ 	n->fromClause = fromClause;
+ 	n->whereClause = NULL;
+ 	n->groupClause = NULL;
+ 	n->havingClause = NULL;
+ 	n->windowClause = NIL;
+ 	n->withClause = NULL;
+ 	n->valuesLists = NIL;
+ 	n->sortClause = NIL;
+ 	n->limitOffset = NULL;
+ 	n->limitCount = NULL;
+ 	n->lockingClause = NIL;
+ 	n->op = SETOP_NONE;
+ 	n->all = false;
+ 	n->larg = NULL;
+ 	n->rarg = NULL;
+ 	return n;
+ }
+ 
+ static List *
+ makeStarTargetList(void)
+ {
+ 	ResTarget *rt = makeNode(ResTarget);
+ 	
+ 	rt->name = NULL;
+ 	rt->indirection = NIL;
+ 	rt->val = (Node *) makeNode(ColumnRef);
+ 	((ColumnRef *) rt->val)->fields = list_make1(makeNode(A_Star));
+ 	rt->location = -1;
+ 
+ 	return list_make1(rt);
+ }
+  
+ static SelectStmt *
+ transformGroupingSets(ParseState *pstate, SelectStmt *stmt)
+ {
+ 	if (stmt->groupClause && IsA(stmt->groupClause, GroupByClause))
+ 	{
+ 		GroupingSetsSpec *gss = (GroupingSetsSpec *) expandGroupingSets(pstate, 
+ 		(List *)((GroupByClause *)stmt->groupClause)->fields);
+ 	
+ 		if (pstate->p_hasGroupingSets)
+ 		{
+ 			CommonTableExpr *cte = makeNode(CommonTableExpr);
+ 			SelectStmt  *cteedstmt;
+ 			int	ngroupingsets = list_length(gss->set_list) + (gss->has_empty_set ? 1 : 0);
+ 			bool	all = ((GroupByClause *) stmt->groupClause)->all;
+ 		
+ 			cteedstmt = makeSelectStmt(NIL, NIL);
+ 			cteedstmt->intoClause = stmt->intoClause;
+ 			cteedstmt->sortClause = stmt->sortClause;
+ 			cteedstmt->limitOffset = stmt->limitOffset;
+ 			cteedstmt->limitCount = stmt->limitCount;
+ 			cteedstmt->lockingClause = stmt->lockingClause;
+ 		
+ 			cte->ctename = "**g**";
+ 			cte->ctequery = (Node *) stmt;
+ 			cte->location = -1;
+ 		
+ 			cteedstmt->withClause = makeNode(WithClause);
+ 			cteedstmt->withClause->ctes = list_make1(cte);
+ 			cteedstmt->withClause->recursive = false;
+ 			cteedstmt->withClause->location = -1;
+ 		
+ 			/* when is more than one grouping set, then we should generate setop node */
+ 			if (ngroupingsets > 1)
+ 			{
+ /* add quuery under union all for every grouping set */
+ SelectStmt	*larg = NULL;
+ SelectStmt	*rarg;
+ ListCell*lc;
+ 			
+ foreach(lc, gss->set_list)
+ {
+ 	List	*groupClause;
+ 
+ 	Assert(IsA(lfirst(lc), List));
+ 	groupClause = (List *) lfirst(lc);
+ 
+ 	if (larg == NULL)
+ 	{
+ 		larg = makeSelectStmt(copyObject(stmt->targetList),
+ 	list_make1(makeRangeVar(NULL, "**g**", -1)));
+ 		larg->groupClause = (Node *) groupClause;
+ 		larg->havingClause = copyObject(stmt->havingClause);
+ 	}
+ 	else
+ 	{
+ 		SelectStmt	*setop = makeSelectStmt(NIL, NIL);
+ 	
+ 		rarg

[HACKERS] Grouping sets

2009-10-29 Thread Pavel Stehule 
Hello

I got a possibility to continue on GROUPING SETS see
http://wiki.postgresql.org/wiki/Grouping_Sets

My last patch was based on an sharing functionality with non recursive
CTE. I would to recapitulate all options here and I would to prepare
this patch for next commitfest.

a) using actualised patch - review:
http://archives.postgresql.org/message-id/162867790811240316y52227d88xe53527399b329...@mail.gmail.com
  + it is simple (share lot of code)
  + it is near to natural implementation - result is in "good" order
  + allows full implementation of ANSI SQL syntax - GROUP BY
[>>ALL<<|DISTINCT] GROUPING SETS(..
  - is serial
  - I read some objections - "it is only syntactic sugar"

b) modification of a (directly using tuple store without CTE code)
  + add some new code
  - it isn't syntactic sugar

c) go back to my first patch and use "feeders"
  - add some complexity to GROUP BY planner ( :-( )
  +/- add new concept to PostgreSQL - semi parallel execution
  - when datatype isn't hashable, then have to use serial processing
  - have to solve some corner behave of our hashtable support
  - should be very fast

I still prefer option a (with some cleaning)

I invite any comments and helpers.

Regards
Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers