Re: foreign join error "variable not found in subplan target list"

On Sun, Aug 21, 2022 at 8:31 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
wrote:

> * I think we should avoid modifying the pathtarget, because it would
> be the default pathtarget, which other paths might reference. I think
> it’s safe to use a copied pathtarget, like the attached.

Yeah, that's right. The EPQ path is a shallow copy of the original. A
copied pathtarget should be saner here.

>
> * I think this issue occurs also when there are PlaceHolderVars in the
> relation’s reltarget. Here is an example:
>
> EXPLAIN (VERBOSE, COSTS OFF)
> SELECT * FROM local_tbl LEFT JOIN (SELECT ft1.*, COALESCE(ft1.c3 ||
> ft2.c3, 'foobar') FROM ft1 INNER JOIN ft2 ON (ft1.c1 = ft2.c1 AND
> ft1.c1 < 100)) ss ON (local_tbl.c1 = ss.c1) ORDER BY local_tbl.c1 FOR
> UPDATE OF local_tbl;
> ERROR: variable not found in subplan target list
>
> where local_tbl, ft1, and ft2 are local/foreign tables defined as in
> postgres_fdw.sql. To fix, I modified the patch so that we add to the
> pathtarget not only columns required for evaluating the local
> conditions but columns required for evaluating the PlaceHoderVars.

Correct. Good catch! I can reproduce this error regarding PHVs in master
and verify that this change can fix it. It makes the logic of adjusting
the pathtarget for the epq_path in add_paths_with_pathkeys_for_rel be
more consistent with the logic in build_tlist_to_deparse, which is good.

>
> * The test case reported in this thread produces the planner error
> only in v14 and later, but I think this issue exists since v9.6. So I
> created/added new test cases, including the above one, that would
> otherwise produce the error even in previous versions.

+1 for the tests.

Thanks
Richard