Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

> > * Single Evaluation:
> >
> > with
> > foo(i) as (select random() as i)
> > select * from foo union all select * from foo;
> > i
> > -------------------
> > 0.233165248762816
> > 0.62126633618027
> > (2 rows)
> >
> > The standard specifies that non-recursive WITH should be evaluated
> > once.
>
> What shall we do? I don't think there's an easy way to fix this as Tom
> suggested. Maybe we should not allow WITH clause without RECURISVE for
> 8.4?

This is a still remaing issue...

> > * Binary recursion and subselect strangeness:
> >
> > with recursive foo(i) as
> > (values (1)
> > union all
> > select * from
> > (select i+1 from foo where i < 10
> > union all
> > select i+1 from foo where i < X) t)
> > select * from foo;
> >
> > Produces 10 rows of output regardless of what "X" is. This should be
> > fixed for 8.4.
> > Also, this is non-linear recursion, which the standard seems to
> > disallow.
>
> I will try to fix this. However detecting the query being not a
> non-linear one is not so easy.

I have implemented rejection of non-linear recursion and now this type
of query will not be executed anyway.

> > * Multiple recursive references:
> >
> > with recursive foo(i) as
> > (values (1)
> > union all
> > select i+1 from foo where i < 10
> > union all
> > select i+1 from foo where i < 20)
> > select * from foo;
> > ERROR: Left hand side of UNION ALL must be a non-recursive term in a
> > recursive query
> >
> > If we're going to allow non-linear recursion (which the standard
> > does not), this seems like it should be a valid case.
>
> I will try to disallow this.

Non-linear recursion is not allowed now.

> > * Strange result with except:
> >
> > with recursive foo(i) as
> > (values (1)
> > union all
> > select * from
> > (select i+1 from foo where i < 10
> > except
> > select i+1 from foo where i < 5) t)
> > select * from foo;
> > ERROR: table "foo" has 0 columns available but 1 columns specified
> >
> > This query works if you replace "except" with "union". This should be
> > fixed for 8.4.
>
> I will try to fix this.

This is a non-linear recursion too and will not be executed anyway.

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

Fixed.

> > * DISTINCT should supress duplicates:
> >
> > with recursive foo(i) as
> > (select distinct * from (values(1),(2)) t
> > union all
> > select distinct i+1 from foo where i < 10)
> > select * from foo;
> >
> > This outputs a lot of duplicates, but they should be supressed
> > according to the standard. This query is essentially the same as
> > supporting UNION for recursive queries, so we should either fix both for
> > 8.4 or block both for consistency.
>
> I'm not sure if it's possible to fix this. Will look into.

Ok, now this type of DISTINCT is not allowed.

Included is the latest patches against CVS HEAD.
--
Tatsuo Ishii
SRA OSS, Inc. Japan