Views on aggregates - need assistence

Hi,

the first step on views using aggregate was successful. But
I don't know enough about GROUP BY, HAVING and all the other
stuff. So I need someone to assist me in getting the rewrite
system to handle this kind of views properly.

The patch below is only for hackers ON THIS TODO TOPIC, it
makes things temporary worse!!!

What works with it:

create table t1 (k int4);
CREATE
insert into t1 values (1);
INSERT 18441 1
insert into t1 values (2);
INSERT 18442 1
insert into t1 values (3);
INSERT 18443 1

create table t2 (a int4, k int4);
CREATE
insert into t2 values (1, 1);
INSERT 18454 1
insert into t2 values (2, 1);
INSERT 18455 1
insert into t2 values (3, 1);
INSERT 18456 1
insert into t2 values (4, 2);
INSERT 18457 1
insert into t2 values (5, 2);
INSERT 18458 1

create view v1 as
select k, count(t2.a) from t1 where t1.k = t2.k
group by k;
CREATE

select * from v1;
k|count
-+-----
1| 3
2| 2
(2 rows)

I don't know if it's right that no row with k=3 shows up. I
had expected a row 'k=3 count=0'. But it's exactly what the
select statement without the view returns. So it's not a
problem of the rewrite system any more.

But doing

select k from v1;

still crashes the backend (though somewhere completely
different).

Before going into details and fixing more things I must have
some different view definitions and table settings (including
nested views with and without aggregates) and what they are
expected to output!

And please some complicated select statements building joins
from tables and the views with GROUP BY etc. too if possible.

As I don't know enough about what GROUP BY really should do I
cannot work out all these test cases myself.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #

diff -c -r rewrite.old/rewriteHandler.c rewrite/rewriteHandler.c
*** rewrite.old/rewriteHandler.c Mon Feb 23 18:09:44 1998
--- rewrite/rewriteHandler.c Mon Feb 23 18:43:30 1998
***************
*** 354,359 ****
--- 354,366 ----
ChangeVarNodes(rule_qual, PRS2_CURRENT_VARNO + rt_length, rt_index, 0);
if (relation_level)
{
+ OffsetVarNodes((Node *)rule_action->groupClause, rt_length);
+ ChangeVarNodes((Node *)rule_action->groupClause,
+ PRS2_CURRENT_VARNO + rt_length, rt_index, 0);
+ parsetree->groupClause = nconc(parsetree->groupClause,
+ copyObject(rule_action->groupClause));
+ parsetree->hasAggs = rule_action->hasAggs;
+ parsetree->hasSubLinks = rule_action->hasSubLinks;
HandleViewRule(parsetree, rtable, rule_action->targetList, rt_index,
modified);
}
Only in rewrite: rewriteHandler.c.swp
diff -c -r rewrite.old/rewriteManip.c rewrite/rewriteManip.c
*** rewrite.old/rewriteManip.c Mon Feb 23 18:09:44 1998
--- rewrite/rewriteManip.c Mon Feb 23 18:36:21 1998
***************
*** 79,84 ****
--- 79,91 ----
}
}
break;
+ case T_GroupClause:
+ {
+ GroupClause *grp = (GroupClause *) node;
+
+ OffsetVarNodes((Node *) grp->entry, offset);
+ }
+ break;
default:
/* ignore the others */
break;
***************
*** 140,145 ****
--- 147,159 ----

ChangeVarNodes((Node *)query->qual, old_varno, new_varno,
sublevels_up + 1);
+ }
+ break;
+ case T_GroupClause:
+ {
+ GroupClause *grp = (GroupClause *) node;
+
+ ChangeVarNodes((Node *) grp->entry, old_varno, new_varno, sublevels_up);
}
break;
default: