Re: Assert failure in CTE inlining with view and correlated subquery

On Thu, Apr 21, 2022 at 5:33 AM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:

it seems there's something wrong with CTE inlining when there's a view
containing a correlated subquery referencing the CTE. 

BTW, seems view is not a necessary condition to reproduce this issue.

For instance:

create table t (a int, b int);

explain (costs off) select a from
(
    with t_cte as (select a, b from t)
    select
        a,
        (select b from t_cte where t_cte.a = t.a) AS t_sub
    from t
) sub;

removed when generating paths for the subquery 'sub', where we would try
to remove subquery targetlist items that are not needed. So for the
items we are to remove, maybe we need to check if they contain CTEs and
if so decrease cterefcount of the CTEs correspondingly.

Thanks
Richard