Re: WITH RECUSIVE patches 0723

At David's request I've been looking through this patch.

Regarding documentation: if it would help, I can write some; I have
already made a start on writing down what is going on internally in
order to understand it myself.

I've found three more bugs so far:

1)

create view v2(id) as values (1);
with recursive t(id) as (select id from v2                        union all select id+1 from t where id < 5)
select * from t;
ERROR:  could not open relation 1663/16384/24588: No such file or directory

Here it seems that rewriting is simply not being applied to CTEs where
a recursive clause is present; the reference to "v2" remains in the
query up until execution time, at which point it errors out (in
ExecInitSeqScan called from InitPlan).

2)

with recursive t(id) as (values (1)                        union all select id+1 from t where id < 5
   union all values (2))
 
select * from t;
ERROR:  table "t" has 0 columns available but 1 columns specified

This seems to be caused by incorrect assumptions in checkWellFormedCte
and checkCteSelectStmt (which should have been rejecting the query).
The query tree as seen by checkWellFormedCte here is (values(1) union
all select ...) union all (values (2)), and when the left subtree is
passed to checkCteSelectStmt, it believes it to be non-recursive due
to the lack of any From clause. The unexpected error is produced
later.

3)

with recursive t(id) as (values (1)     union all select t.id+1                 from t left join (values (1)) as s(x)
on(false)                where t.id < 5)
 
select * from t;id 
---- 1 2
(2 rows)

This behaviour is clearly intentional, since the entire mechanism of
estate->es_disallow_tuplestore exists for no other reason, but it
seems to me to be clearly wrong. What is the justification for it?

-- 
Andrew (irc:RhodiumToad)