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

On Thu, Apr 21, 2022 at 3:51 PM Richard Guo <guofenglinux@gmail.com> wrote:

>
> 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;
>

Further debugging shows that in this repro the reference to the CTE is
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