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

On 2021-09-16 08:40, 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.
> 
>             regards, tom lane

Thanks for looking into this and fixing it!

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION