Feedback-ID: ie8b149e1:Fastmail
User-Agent: Cyrus-JMAP/3.9.0-alpha0-711-g440737448e-fm-20230828.001-g44073744
Mime-Version: 1.0
Message-Id: <3933834e-b657-4ad1-bf4e-5f3fbba7ba14@app.fastmail.com>
In-Reply-To: 
 <CAMbWs481eExv88QMG1p18sFyjRxvLfiiOHA+OoJYssPcx5ap+w@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>
 <3f7bcdb7-c263-4c06-a138-140f5c3898ed@app.fastmail.com>
 <CAMbWs481eExv88QMG1p18sFyjRxvLfiiOHA+OoJYssPcx5ap+w@mail.gmail.com>
Date: Thu, 07 Sep 2023 15:25:36 +0700
From: "Lepikhov Andrei" <a.lepikhov@postgrespro.ru>
To: "Richard Guo" <guofenglinux@gmail.com>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, 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: text/plain;charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/3933834e-b657-4ad1-bf4e-5f3fbba7ba14%40app.fastmail.com>
Precedence: bulk


On Wed, Sep 6, 2023, at 1:55 PM, Richard Guo wrote:
> On Wed, Sep 6, 2023 at 11:40=E2=80=AFAM Lepikhov Andrei=20
> <a.lepikhov@postgrespro.ru> wrote:
>> Hi,
>>=20
>> 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:
>>=20
>> 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;
>>=20
>> but fails with the error "failed to find plan for CTE ..." with a cus=
tom node over a JOIN.=20
>
> The error message indicates that something must have gone wrong.  I
> don't know well enough about custom scan, but I cannot reproduce this
> error with your query.  Am I missing something?
I invented a dummy extension "pg_extension" [1], commit 4199a0c, which a=
dds  CustomScan over the first non-parameterized HashJoin at the pathlis=
t.
The example presented in my letter earlier causes the ERROR on CTE. More=
over, if you remove the word 'materialized', you will find the same erro=
r on Subquery.

[1] https://github.com/danolivo/pg_extension/tree/main

--=20
Regards,
Andrei Lepikhov