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

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.