Re: Common Table Expressions (WITH RECURSIVE) patch

> > * Aggregates allowed:
> >
> > with recursive foo(i) as
> > (values(1)
> > union all
> > select max(i)+1 from foo where i < 10)
> > select * from foo;
> >
> > Aggregates should be blocked according to the standard.
> > Also, causes an infinite loop. This should be fixed for 8.4.
>
> I will try to fix this.

We already reject:

select max(i) from foo where i < 10)

But max(i)+1 seems to slip the check. I looked into this I found the
patch tried to detect the case before analyzing(see
parser/parse_cte.c) which is not a right thing I think.

I think we could detect the case by adding more checking in
parseCheckAggregates():

/*
* Check if there's aggregate function in a recursive term.
*/
foreach(l, qry->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);

if (qry->hasAggs && rte->rtekind == RTE_RECURSIVE &&
rte->self_reference)
{
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("aggregate functions in a recursive term not allowed")));
}
}

What do you think?
--
Tatsuo Ishii
SRA OSS, Inc. Japan