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

Hi!

It seems like this patch causes another problem.

If I explain a simple row generator **without** verbose, it fails:

postgres=# EXPLAIN (VERBOSE FALSE)
WITH RECURSIVE gen (n) AS (
VALUES (1)
UNION ALL
SELECT n+1
FROM gen
WHERE n < 3
)
SELECT * FROM gen
;
ERROR: could not find RecursiveUnion for WorkTableScan with wtParam 0

That’s the new error message introduced by the patch.

The same with verbose works just fine:

postgres=# EXPLAIN (VERBOSE TRUE)
WITH RECURSIVE gen (n) AS (
VALUES (1)
UNION ALL
SELECT n+1
FROM gen
WHERE n < 3
)
SELECT * FROM gen
;
QUERY PLAN
-----------------------------------------------------------------------------
CTE Scan on gen (cost=2.95..3.57 rows=31 width=4)
Output: gen.n
CTE gen
-> Recursive Union (cost=0.00..2.95 rows=31 width=4)
-> Result (cost=0.00..0.01 rows=1 width=4)
Output: 1
-> WorkTable Scan on gen gen_1 (cost=0.00..0.23 rows=3 width=4)
Output: (gen_1.n + 1)
Filter: (gen_1.n < 3)
(9 rows)

Both variants work fine before that patch (4ac0f450b698442c3273ddfe8eed0e1a7e56645f).

Markus Winand
winand.at

> On 21.09.2021, at 14:43, torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> wrote:
>
> 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
>
>
>
>