MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Tue, 21 Sep 2021 21:43:14 +0900
From: torikoshia <torikoshia@oss.nttdata.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Andrew Dunstan
 <andrew@dunslane.net>, Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: Re: EXPLAIN(VERBOSE) to CTE with SEARCH BREADTH FIRST fails
In-Reply-To: <1126187.1631749231@sss.pgh.pa.us>
References: <5bafa66ad529e11860339565c9e7c166@oss.nttdata.com>
 <3208199.1631039493@sss.pgh.pa.us>
 <b6fecc71-60b9-bd44-4b78-f2a9dd74f46f@enterprisedb.com>
 <d70fa4dfd333b1de1024e50c2ad1a6ea@oss.nttdata.com>
 <24ce73f9-c714-9a7c-6cc0-34e724c8f1c9@dunslane.net>
 <984394.1631719708@sss.pgh.pa.us> <1126187.1631749231@sss.pgh.pa.us>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <7da9d40759bab675e57ab495499720c1@oss.nttdata.com>
Archived-At: 
 <https://www.postgresql.org/message-id/7da9d40759bab675e57ab495499720c1%40oss.nttdata.com>
Precedence: bulk

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