Re: BUG #18170: Unexpected error: no relation entry for relid 3

On Tue, Oct 31, 2023 at 3:37 PM Richard Guo <guofenglinux@gmail.com> wrote:

> Well, I think what happens here is that the new SJE code alters the
> subquery (by removing ref_2) and that change is not propagated into the
> parent level.  So in the parent level, when we look at the subquery's
> original targetlist (which remains unchanged) against 'subroot' (which
> has been changed accordingly), we'd have problem.
>
> AFAICS there are two solutions being discussed:
>
> 1) We propagate the change to the subquery into the parent level by
> ensuring that root->parse references the same Query structure during the
> whole subquery_planner().
>
> 2) In the parent level, we look at the changed subquery instead of the
> original rte->subquery.  IOW, we look at subroot->parse->targetList
> instead of subquery->targetList.
>
> Personally I prefer the second solution because it seems more natural to
> look at 'subroot->parse' together with 'subroot' in estimate_num_groups
> and it does not introduce new constraint to subquery_planner().
>

FWIW, here is a simplified query that can reproduce this error.

explain (costs off)
select ref_1.c17 from t2 ref_1 left join
        t2 ref_2 on ref_1.vkey = ref_2.vkey
where ref_2.vkey is not null
except all
select c17 from t2 ref_3;
ERROR:  no relation entry for relid 1

Thanks
Richard