Re: [HACKERS] 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.

I think the join properly eliminates the k=3 row.  The aggregate happens
after the join.

For the aggregate gory details, see backend/optimizer/plan/planner.c.
You will see how GROUP and Agg nodes are inserted above the tree to then
be handled by the executor.  Hopefully if the rewrite system works, the
change will be transparent to the optimizer, but you have to set the
query Aggreg fields properly when doing this.  You can also look at
parser/parse_agg.c to see how a normal aggregate coming in from the
parser is configured.

One other cool way of doing testing is to run the backend with -d3 debug
level, and then look at the post-rewrite trees for an aggregate query
and an aggregate from a view, and see if they are the same.  The output
shows almost all the fields in the query.

>
>     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:
>
>


--
Bruce Momjian
maillist@candle.pha.pa.us