Views on aggregates - need assistence - Mailing list pgsql-hackers

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@debis.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: