Feedback-ID: ie8b149e1:Fastmail
User-Agent: Cyrus-JMAP/3.9.0-alpha0-711-g440737448e-fm-20230828.001-g44073744
Mime-Version: 1.0
Message-Id: <3f7bcdb7-c263-4c06-a138-140f5c3898ed@app.fastmail.com>
In-Reply-To: 
 <CAMbWs4_i5+xB1WQ5SVOe8SQscaMswx8hbbgA1YKmtAD-vgcxnA@mail.gmail.com>
References: <18077-b9db97c6e0ab45d8@postgresql.org>
 <CAMbWs4-4mDc9j5t+d3MbOSFSTqCDAHQKVV8zE8yiwsaXFGHL9Q@mail.gmail.com>
 <CAMbWs4_zxFFSy8+6-FG2ZdJc91TQGM+yG0Yh7gydhjTQfb=9fg@mail.gmail.com>
 <694541.1693600870@sss.pgh.pa.us>
 <CAMbWs49PRmEWmR0BKG-r8Me0kwr1iLo96diFenhtHYGCvcGgvw@mail.gmail.com>
 <1237514.1693879473@sss.pgh.pa.us>
 <CAMbWs4_i5+xB1WQ5SVOe8SQscaMswx8hbbgA1YKmtAD-vgcxnA@mail.gmail.com>
Date: Wed, 06 Sep 2023 10:39:56 +0700
From: "Lepikhov Andrei" <a.lepikhov@postgrespro.ru>
To: "Richard Guo" <guofenglinux@gmail.com>, "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: fuboat@outlook.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18077: PostgreSQL server subprocess crashed by a SELECT
 statement
 with WITH clause
Content-Type: multipart/mixed;
 boundary=b1e0540e128d40aebcf620300348ed49
Archived-At: 
 <https://www.postgresql.org/message-id/3f7bcdb7-c263-4c06-a138-140f5c3898ed%40app.fastmail.com>
Precedence: bulk

--b1e0540e128d40aebcf620300348ed49
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi,

I am writing here just because you change this specific part of code.
Designing a custom node I found the problem with CTE and Subqueries. The=
 reproduction sample looks quite similar to yours:

create view tt24v as
with cte as materialized (select r from (values(1,2),(3,4)) r)
select (r).column2 as col_a, (rr).column2 as col_b from
  cte join (select rr from (values(1,7),(3,8)) rr limit 2) ss
  on (r).column1 =3D (rr).column1;
explain (verbose, costs off) select * from tt24v;

but fails with the error "failed to find plan for CTE ..." with a custom=
 node over a JOIN. Doing a trick like in trick.diff in attachment, I can=
 obtain the next plan:

 Result
   Output: (cte.r).column2, (ss.rr).column2
   CTE cte
     ->  Values Scan on "*VALUES*_2"
           Output: ROW("*VALUES*_2".column1, "*VALUES*_2".column2)
   ->  Custom Scan (XXX)
         Output: cte.r, ss.rr
         ->  Hash Join
               Output: cte.r, (ROW("*VALUES*".column1, "*VALUES*".column=
2))
               Hash Cond: ((cte.r).column1 =3D ((ROW("*VALUES*".column1,=
 "*VALUES*".column2))).column1)
               ->  CTE Scan on cte
                     Output: cte.r
               ->  Hash
                     Output: (ROW("*VALUES*".column1, "*VALUES*".column2=
))
                     ->  Limit
                           Output: (ROW("*VALUES*".column1, "*VALUES*".c=
olumn2))
                           ->  Values Scan on "*VALUES*"
                                 Output: ROW("*VALUES*".column1, "*VALUE=
S*".column2)

The result node in attempt to deparse it's targetlist goes into OUTER_VA=
R - Custom node. After that it goes through the INDEX_VAR ref to custom_=
scan_tlist, finds reference to the RangeTableEntry CTE, empty dpns->inne=
r_plan and throws the error.

As you can see, the problem here is in wrong assumption: custom_scan_tli=
st can contain direct references to CTEs and Subqueries as well as WorkT=
ableScan or CteScan.
Maybe to solve this problem too?

--=20
Regards,
Andrei Lepikhov

On Tue, Sep 5, 2023, at 9:37 AM, Richard Guo wrote:
> On Tue, Sep 5, 2023 at 10:04=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> w=
rote:
>> Richard Guo <guofenglinux@gmail.com> writes:
>> > BTW, do you think get_name_for_var_field() has similar problem for
>> > RTE_SUBQUERY case?  The RTE_CTE code path in that function crawls u=
p the
>> > namespace stack before recursing into the CTE while the RTE_SUBQUERY
>> > code patch does not, which looks like an oversight.
>>=20
>> Hmm, seems suspicious ...
>>=20
>> > I tried to find a
>> > test case to show it's indeed a problem but with no luck.
>>=20
>> Note that any test case here would be of the form "dump a view
>> or rule definition", not "EXPLAIN".  What did you try?
>
> Ah, thanks.  I got one of the form "dump a view" leveraging your test
> case from the v2 patch (with a minor tweak).
>
> create view composite_v as
> with cte(c) as materialized (select row(1, 2)),
>      cte2(c) as (select * from cte)
> select 1 from cte2 as t
> where (select * from (select c as c1) s
>        where (select (c1).f1 > 0)) is not null;
>
> select pg_get_viewdef('composite_v', true);
> ERROR:  bogus varno: 1
>
> So it is indeed a problem!
>
> Here is v3 patch which is v2 + fix for this issue.

--b1e0540e128d40aebcf620300348ed49
Content-Disposition: attachment; filename="trick.diff"
Content-Type: application/octet-stream; name="trick.diff"
Content-Transfer-Encoding: BASE64

ZGlmZiAtLWdpdCBhL2NvbnRyaWIvTWFrZWZpbGUgYi9jb250cmliL01ha2VmaWxlCmluZGV4
IGJiZjIyMDQwN2IuLjdkOTdiNjkzYjAgMTAwNjQ0Ci0tLSBhL2NvbnRyaWIvTWFrZWZpbGUK
KysrIGIvY29udHJpYi9NYWtlZmlsZQpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRpbHMv
YWR0L3J1bGV1dGlscy5jIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3J1bGV1dGlscy5jCmlu
ZGV4IDk3YjBlZjIyYWMuLjA5NWFkM2RiMjQgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0
aWxzL2FkdC9ydWxldXRpbHMuYworKysgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvcnVsZXV0
aWxzLmMKQEAgLTUwMDQsNiArNTAwNCw4IEBAIHNldF9kZXBhcnNlX3BsYW4oZGVwYXJzZV9u
YW1lc3BhY2UgKmRwbnMsIFBsYW4gKnBsYW4pCiAJZWxzZSBpZiAoSXNBKHBsYW4sIFdvcmtU
YWJsZVNjYW4pKQogCQlkcG5zLT5pbm5lcl9wbGFuID0gZmluZF9yZWN1cnNpdmVfdW5pb24o
ZHBucywKIAkJCQkJCQkJCQkJCShXb3JrVGFibGVTY2FuICopIHBsYW4pOworCWVsc2UgaWYg
KElzQShwbGFuLCBDdXN0b21TY2FuKSkKKwkJZHBucy0+aW5uZXJfcGxhbiA9IChQbGFuICop
IGxpbml0aWFsKGRwbnMtPnN1YnBsYW5zKTsKIAllbHNlIGlmIChJc0EocGxhbiwgTW9kaWZ5
VGFibGUpKQogCQlkcG5zLT5pbm5lcl9wbGFuID0gcGxhbjsKIAllbHNlCg==

--b1e0540e128d40aebcf620300348ed49--