MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="=_235512faf3b5f7f5d43b34d2102e73c2"
Content-Transfer-Encoding: 7bit
Date: Fri, 10 Sep 2021 23:10:43 +0900
From: torikoshia <torikoshia@oss.nttdata.com>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Pgsql Hackers
 <pgsql-hackers@postgresql.org>
Subject: Re: EXPLAIN(VERBOSE) to CTE with SEARCH BREADTH FIRST fails
In-Reply-To: <b6fecc71-60b9-bd44-4b78-f2a9dd74f46f@enterprisedb.com>
References: <5bafa66ad529e11860339565c9e7c166@oss.nttdata.com>
 <3208199.1631039493@sss.pgh.pa.us>
 <b6fecc71-60b9-bd44-4b78-f2a9dd74f46f@enterprisedb.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <d70fa4dfd333b1de1024e50c2ad1a6ea@oss.nttdata.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d70fa4dfd333b1de1024e50c2ad1a6ea%40oss.nttdata.com>
Precedence: bulk

--=_235512faf3b5f7f5d43b34d2102e73c2
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII;
 format=flowed

On 2021-09-09 19:03, Peter Eisentraut wrote:
> On 07.09.21 20:31, Tom Lane wrote:
>> torikoshia <torikoshia@oss.nttdata.com> writes:
>>> While working on [1], we found that EXPLAIN(VERBOSE) to CTE with 
>>> SEARCH
>>> BREADTH FIRST ends up ERROR.
>> 
>> Yeah.  It's failing here:
>> 
>>                       * We're deparsing a Plan tree so we don't have a 
>> CTE
>>                       * list.  But the only place we'd see a Var 
>> directly
>>                       * referencing a CTE RTE is in a CteScan plan 
>> node, and we
>>                       * can look into the subplan's tlist instead.
>> 
>>                      if (!dpns->inner_plan)
>>                          elog(ERROR, "failed to find plan for CTE %s",
>>                               rte->eref->aliasname);
>> 
>> The problematic Var is *not* in a CteScan plan node; it's in a
>> WorkTableScan node.  It's not clear to me whether this is a bug
>> in the planner's handling of SEARCH BREADTH FIRST, or if the plan
>> is as-intended and ruleutils.c is failing to cope.
> 
> The search clause is resolved by the rewriter, so it's unlikely that
> the planner is doing something wrong.  Either the rewriting produces
> something incorrect (but then one might expect that the query results
> would be wrong), or the structures constructed by rewriting are not
> easily handled by ruleutils.c.
> 
> If we start from the example in the documentation
> <https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE>:
> 
> """
> WITH RECURSIVE search_tree(id, link, data, depth) AS (
>     SELECT t.id, t.link, t.data, 0
>     FROM tree t
>   UNION ALL
>     SELECT t.id, t.link, t.data, depth + 1
>     FROM tree t, search_tree st
>     WHERE t.id = st.link
> )
> SELECT * FROM search_tree ORDER BY depth;
> 
> To get a stable sort, add data columns as secondary sorting columns.
> """
> 
> In order to handle that part about the stable sort, the query
> constructed internally is something like
> 
> WITH RECURSIVE search_tree(id, link, data, seq) AS (
>     SELECT t.id, t.link, t.data, ROW(0, id, link)
>     FROM tree t
>   UNION ALL
>     SELECT t.id, t.link, t.data, ROW(seq.depth + 1, id, link)
>     FROM tree t, search_tree st
>     WHERE t.id = st.link
> )
> SELECT * FROM search_tree ORDER BY seq;
> 
> The bit "seq.depth" isn't really valid when typed in like that, I
> think, but of course internally this is all wired together with
> numbers rather than identifiers.  I suspect that that is what
> ruleutils.c trips over.

Thanks for your advice, it seems right.

EXPLAIN VERBOSE can be output without error when I assigned testing 
purpose CoercionForm to 'seq.depth + 1'.

I've attached the patch for the changes made for this test for your 
reference, but I'm not sure it's appropriate for creating a new 
CoercionForm to fix the issue..

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION
--=_235512faf3b5f7f5d43b34d2102e73c2
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=v1-0001-fix_err_explain_verbose_cte_breadth.patch
Content-Disposition: attachment;
 filename=v1-0001-fix_err_explain_verbose_cte_breadth.patch;
 size=1736

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZVNlYXJjaEN5Y2xlLmMgYi9z
cmMvYmFja2VuZC9yZXdyaXRlL3Jld3JpdGVTZWFyY2hDeWNsZS5jCmluZGV4IGM1MGViZGJhMjQu
LjY1OWE1YzQ0MGYgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZVNlYXJj
aEN5Y2xlLmMKKysrIGIvc3JjL2JhY2tlbmQvcmV3cml0ZS9yZXdyaXRlU2VhcmNoQ3ljbGUuYwpA
QCAtNTA4LDcgKzUwOCw3IEBAIHJld3JpdGVTZWFyY2hBbmRDeWNsZShDb21tb25UYWJsZUV4cHIg
KmN0ZSkKIAkJCWZzLT5yZXN1bHR0eXBlID0gSU5UOE9JRDsKIAkJCWZzLT5yZXN1bHR0eXBtb2Qg
PSAtMTsKIAotCQkJZmV4cHIgPSBtYWtlRnVuY0V4cHIoRl9JTlQ4SU5DLCBJTlQ4T0lELCBsaXN0
X21ha2UxKGZzKSwgSW52YWxpZE9pZCwgSW52YWxpZE9pZCwgQ09FUkNFX0VYUExJQ0lUX0NBTEwp
OworCQkJZmV4cHIgPSBtYWtlRnVuY0V4cHIoRl9JTlQ4SU5DLCBJTlQ4T0lELCBsaXN0X21ha2Ux
KGZzKSwgSW52YWxpZE9pZCwgSW52YWxpZE9pZCwgQ09FUkNFX05PTUlOQUxfQ0FMTCk7CiAKIAkJ
CWxmaXJzdChsaXN0X2hlYWQoc2VhcmNoX2NvbF9yb3dleHByLT5hcmdzKSkgPSBmZXhwcjsKIApk
aWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3J1bGV1dGlscy5jIGIvc3JjL2JhY2tl
bmQvdXRpbHMvYWR0L3J1bGV1dGlscy5jCmluZGV4IDhmZjRlNWRjMDcuLmM1YTVmYTA2ZjMgMTAw
NjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9ydWxldXRpbHMuYworKysgYi9zcmMvYmFj
a2VuZC91dGlscy9hZHQvcnVsZXV0aWxzLmMKQEAgLTk2NDgsNiArOTY0OCwxMiBAQCBnZXRfZnVu
Y19leHByKEZ1bmNFeHByICpleHByLCBkZXBhcnNlX2NvbnRleHQgKmNvbnRleHQsCiAJCQlyZXR1
cm47CiAJfQogCisJaWYgKGV4cHItPmZ1bmNmb3JtYXQgPT0gQ09FUkNFX05PTUlOQUxfQ0FMTCkK
Kwl7CisJCWFwcGVuZFN0cmluZ0luZm8oY29udGV4dC0+YnVmLCAibm9taW5hbCBmdW5jIik7CisJ
CXJldHVybjsKKwl9CisKIAkvKgogCSAqIE5vcm1hbCBmdW5jdGlvbjogZGlzcGxheSBhcyBwcm9u
YW1lKGFyZ3MpLiAgRmlyc3Qgd2UgbmVlZCB0byBleHRyYWN0CiAJICogdGhlIGFyZ3VtZW50IGRh
dGF0eXBlcy4KZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRlL25vZGVzL3ByaW1ub2Rlcy5oIGIvc3Jj
L2luY2x1ZGUvbm9kZXMvcHJpbW5vZGVzLmgKaW5kZXggYzA0MjgyZjkxZi4uYTNkNjAwZDFhMiAx
MDA2NDQKLS0tIGEvc3JjL2luY2x1ZGUvbm9kZXMvcHJpbW5vZGVzLmgKKysrIGIvc3JjL2luY2x1
ZGUvbm9kZXMvcHJpbW5vZGVzLmgKQEAgLTQ4Myw3ICs0ODMsOCBAQCB0eXBlZGVmIGVudW0gQ29l
cmNpb25Gb3JtCiAJQ09FUkNFX0VYUExJQ0lUX0NBTEwsCQkvKiBkaXNwbGF5IGFzIGEgZnVuY3Rp
b24gY2FsbCAqLwogCUNPRVJDRV9FWFBMSUNJVF9DQVNULAkJLyogZGlzcGxheSBhcyBhbiBleHBs
aWNpdCBjYXN0ICovCiAJQ09FUkNFX0lNUExJQ0lUX0NBU1QsCQkvKiBpbXBsaWNpdCBjYXN0LCBz
byBoaWRlIGl0ICovCi0JQ09FUkNFX1NRTF9TWU5UQVgJCQkvKiBkaXNwbGF5IHdpdGggU1FMLW1h
bmRhdGVkIHNwZWNpYWwgc3ludGF4ICovCisJQ09FUkNFX1NRTF9TWU5UQVgsCQkJLyogZGlzcGxh
eSB3aXRoIFNRTC1tYW5kYXRlZCBzcGVjaWFsIHN5bnRheCAqLworCUNPRVJDRV9OT01JTkFMX0NB
TEwKIH0gQ29lcmNpb25Gb3JtOwogCiAvKgo=
--=_235512faf3b5f7f5d43b34d2102e73c2--