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@oss.nttdata.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