Re: BUG #18077: PostgreSQL server subprocess crashed by a SELECT statement with WITH clause - Mailing list pgsql-bugs

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 = (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.diffin 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*".column2))
               Hash Cond: ((cte.r).column1 = ((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*".column2))
                           ->  Values Scan on "*VALUES*"
                                 Output: ROW("*VALUES*".column1, "*VALUES*".column2)

The result node in attempt to deparse it's targetlist goes into OUTER_VAR - Custom node. After that it goes through the
INDEX_VARref to custom_scan_tlist, finds reference to the RangeTableEntry CTE, empty dpns->inner_plan and throws the
error.

As you can see, the problem here is in wrong assumption: custom_scan_tlist can contain direct references to CTEs and
Subqueriesas well as WorkTableScan or CteScan. 
Maybe to solve this problem too?

--
Regards,
Andrei Lepikhov

On Tue, Sep 5, 2023, at 9:37 AM, Richard Guo wrote:
> On Tue, Sep 5, 2023 at 10:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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 up the
>> > namespace stack before recursing into the CTE while the RTE_SUBQUERY
>> > code patch does not, which looks like an oversight.
>>
>> Hmm, seems suspicious ...
>>
>> > I tried to find a
>> > test case to show it's indeed a problem but with no luck.
>>
>> 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.