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

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