Assert failure in CTE inlining with view and correlated subquery

Hi,

it seems there's something wrong with CTE inlining when there's a view
containing a correlated subquery referencing the CTE. Consider a simple
example like this:

    create table results (
      id          serial primary key,
      run         text,
      tps         float4
    );

    create view results_agg as
    with base_tps as (
      select run, tps from results
    )
    select
        run,
        count(*) as runs,

        (select tps from base_tps b where b.run = r.run) AS base_tps

    from results r
    group by
        run
    order by
        run;

    explain SELECT run FROM results_agg ORDER BY 1;


This crashes on this assert in inline_cte():

    Assert(context.refcount == 0);

because the refcount value remains 1. There's a backtrace attached.

I don't know why exactly this happens, my knowledge of CTE inlining is
somewhat limited. The counter is clearly out of sync


but a couple more observations:

1) it fails all the way back to PG12, where CTE inlining was added

2) it does not happen if the CTE is defined as MATERIALIZED

                   QUERY PLAN
    -----------------------------------------
     Subquery Scan on results_agg
       ->  Sort
             Sort Key: r.run
             CTE base_tps
               ->  Seq Scan on results
             ->  HashAggregate
                   Group Key: r.run
                   ->  Seq Scan on results r
    (8 rows)

3) without asserts, it seems to work and the query generates this plan

                   QUERY PLAN
    -----------------------------------------
     Subquery Scan on results_agg
       ->  Sort
             Sort Key: r.run
             ->  HashAggregate
                   Group Key: r.run
                   ->  Seq Scan on results r
    (6 rows)

4) it does not seem to happen without the view, i.e. this works

    explain
    with base_tps as (
      select run, tps from results
    )
    select run from (
      select
        run,
        count(*) as runs,

        (select tps from base_tps b where b.run = r.run) AS base_tps

    from results r
    group by
        run
    order by
        run
    ) results_agg order by 1;

The difference between plans in (2) and (3) is interesting, because it
seems the CTE got inlined, so why was the refcount not decremented?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company