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.

Thanks. There was some docs written in Japanese by Yoshiyuki. Recently
he updagted it. I will translate into English and post here.

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

Yes, we need to make the rewrite system to understand CTEs. Probably
fireRIRrules() needs to have lines something like:

if (rte->rtekind == RTE_RECURSIVE)
{
rte->non_recursive_query = fireRIRrules(rte->non_recursive_query, activeRIRs);
continue;
}

But I still see the error message. Will look into more.

For below, I will ask Yoshiyuki.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> 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)
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers