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

Hi Richard,

On Wed, Aug 10, 2022 at 2:28 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Wed, Aug 10, 2022 at 10:15 AM Richard Guo <guofenglinux@gmail.com> wrote:
>> Currently the outer_plan used in postgresGetForeignPlan() can only be
>> 'Join' or 'Sort + Join'. I'm wondering whether we can take this
>> knowledge into consideration when we fix the outer_plan's tlist, to also
>> fix the Join's tlist if it is below the Sort node.

> Alternatively, how about we include in the EPQ path's pathtarget the
> columns required for evaluating the local conditions when we consider
> EPQ paths with pathkeys? Something like attached.

Thanks for the patch!  I reviewed the patch.  I think the patch goes
in the right direction.

+   if (epq_path != NULL && useful_pathkeys_list != NIL)
+   {
+       /* Include columns required for evaluating the local conditions */
+       foreach(lc, fpinfo->local_conds)
+       {
+           RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+           add_new_columns_to_pathtarget(epq_path->pathtarget,
+                                         pull_var_clause((Node *)
rinfo->clause,
+                                         PVC_RECURSE_PLACEHOLDERS));
+       }
+   }

* 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.

* 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.

* 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.

Best regards,
Etsuro Fujita