Re: [HACKERS] Views on aggregates - need assistence

Bruce wrote:
> > 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.

Then it's OK.

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

That's exactly how I saw that the rewritten parsetree missed
the groupClause from the views select and the hasAgg flag,
and what told me that OffsetVarNodes() and ChangeVarNodes()
didn't handle the T_GroupClause :-)

Anyway - the rewrite system doesn't handle the view queries
sortClause, havingQual and unionClause either. And I really
think doing all this in one step is better than groupClause
now and the others later. But doing all is way too much to be
done properly for 6.3. So I ask for moving all these issues
into the 6.4 TODO.

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) #