Re: EXPLAIN(VERBOSE) to CTE with SEARCH BREADTH FIRST fails

On 2021-09-09 19:03, Peter Eisentraut wrote:
> On 07.09.21 20:31, Tom Lane wrote:
>> torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> writes:
>>> While working on [1], we found that EXPLAIN(VERBOSE) to CTE with
>>> SEARCH
>>> BREADTH FIRST ends up ERROR.
>>
>> Yeah. It's failing here:
>>
>> * We're deparsing a Plan tree so we don't have a
>> CTE
>> * list. But the only place we'd see a Var
>> directly
>> * referencing a CTE RTE is in a CteScan plan
>> node, and we
>> * can look into the subplan's tlist instead.
>>
>> if (!dpns->inner_plan)
>> elog(ERROR, "failed to find plan for CTE %s",
>> rte->eref->aliasname);
>>
>> The problematic Var is *not* in a CteScan plan node; it's in a
>> WorkTableScan node. It's not clear to me whether this is a bug
>> in the planner's handling of SEARCH BREADTH FIRST, or if the plan
>> is as-intended and ruleutils.c is failing to cope.
>
> The search clause is resolved by the rewriter, so it's unlikely that
> the planner is doing something wrong. Either the rewriting produces
> something incorrect (but then one might expect that the query results
> would be wrong), or the structures constructed by rewriting are not
> easily handled by ruleutils.c.
>
> If we start from the example in the documentation
> <https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE>:
>
> """
> WITH RECURSIVE search_tree(id, link, data, depth) AS (
> SELECT t.id, t.link, t.data, 0
> FROM tree t
> UNION ALL
> SELECT t.id, t.link, t.data, depth + 1
> FROM tree t, search_tree st
> WHERE t.id = st.link
> )
> SELECT * FROM search_tree ORDER BY depth;
>
> To get a stable sort, add data columns as secondary sorting columns.
> """
>
> In order to handle that part about the stable sort, the query
> constructed internally is something like
>
> WITH RECURSIVE search_tree(id, link, data, seq) AS (
> SELECT t.id, t.link, t.data, ROW(0, id, link)
> FROM tree t
> UNION ALL
> SELECT t.id, t.link, t.data, ROW(seq.depth + 1, id, link)
> FROM tree t, search_tree st
> WHERE t.id = st.link
> )
> SELECT * FROM search_tree ORDER BY seq;
>
> The bit "seq.depth" isn't really valid when typed in like that, I
> think, but of course internally this is all wired together with
> numbers rather than identifiers. I suspect that that is what
> ruleutils.c trips over.

Thanks for your advice, it seems right.

EXPLAIN VERBOSE can be output without error when I assigned testing
purpose CoercionForm to 'seq.depth + 1'.

I've attached the patch for the changes made for this test for your
reference, but I'm not sure it's appropriate for creating a new
CoercionForm to fix the issue..

--
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION