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

On 9/15/21 7:40 PM, Tom Lane wrote:
> I wrote:
>> I do not think that patch is a proper solution, but we do need to do
>> something about this.
> I poked into this and decided it's an ancient omission within ruleutils.c.
> The reason we've not seen it before is probably that you can't get to the
> case through the parser. The SEARCH stuff is generating a query structure
> basically equivalent to
>
> regression=# with recursive cte (x,r) as (
> select 42 as x, row(i, 2.3) as r from generate_series(1,3) i
> union all
> select x, row((c.r).f1, 4.5) from cte c
> )
> select * from cte;
> ERROR: record type has not been registered
>
> and as you can see, expandRecordVariable fails to figure out what
> the referent of "c.r" is. I think that could be fixed (by looking
> into the non-recursive term), but given the lack of field demand,
> I'm not feeling that it's urgent.
>
> So the omission is pretty obvious from the misleading comment:
> actually, Vars referencing RTE_CTE RTEs can also appear in WorkTableScan
> nodes, and we're not doing anything to support that. But we only reach
> this code when trying to resolve a field of a Var of RECORD type, which
> is a case that it seems like the parser can't produce.
>
> It doesn't look too hard to fix: we just have to find the RecursiveUnion
> that goes with the WorkTableScan, and drill down into that, much as we
> would do in the CteScan case. See attached draft patch. I'm too tired
> to beat on this heavily or add a test case, but I have verified that it
> passes check-world and handles the example presented in this thread.
>
>

Looks like a nice simple fix. Thanks for working on this.

cheers

andrew

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