MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Tue, 07 Sep 2021 12:41:02 +0900
From: torikoshia <torikoshia@oss.nttdata.com>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: EXPLAIN(VERBOSE) to CTE with SEARCH BREADTH FIRST fails
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <5bafa66ad529e11860339565c9e7c166@oss.nttdata.com>
Archived-At: 
 <https://www.postgresql.org/message-id/5bafa66ad529e11860339565c9e7c166%40oss.nttdata.com>
Precedence: bulk

Hi,

While working on [1], we found that EXPLAIN(VERBOSE) to CTE with SEARCH 
BREADTH FIRST ends up ERROR.

This can be reproduced at the current HEAD(4c3478859b7359912d7):

   =# create table graph0( f int, t int, label text);
   CREATE TABLE

   =# insert into graph0 values (1, 2, 'arc 1 -> 2'),(1, 3, 'arc 1 -> 
3'),(2, 3, 'arc 2 -> 3'),(1, 4, 'arc 1 -> 4'),(4, 5, 'arc 4 -> 5');
   INSERT 0 5

   =# explain(verbose) with recursive search_graph(f, t, label) as (
       select * from graph0 g
       union all
       select g.*
       from graph0 g, search_graph sg
       where g.f = sg.t
   ) search breadth first by f, t set seq
   select * from search_graph order by seq;
   ERROR:  failed to find plan for CTE sg

Is this a bug?


[1] 
https://www.postgresql.org/message-id/flat/cf8501bcd95ba4d727cbba886ba9eea8@oss.nttdata.com

Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION