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

On 9/10/21 10:10 AM, torikoshia wrote:
> 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..



This is listed as an open item for release 14. Is it planned to commit
the patch? If not, we should close the item.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com