Re: Common Table Expressions (WITH RECURSIVE) patch - Mailing list pgsql-hackers

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