Fix planner problems with LATERAL references in PlaceHolderVars.

The planner largely failed to consider the possibility that a
PlaceHolderVar's expression might contain a lateral reference to a Var
coming from somewhere outside the PHV's syntactic scope.  We had a previous
report of a problem in this area, which I tried to fix in a quick-hack way
in commit 4da6439bd8553059766011e2a42c6e39df08717f, but Antonin Houska
pointed out that there were still some problems, and investigation turned
up other issues.  This patch largely reverts that commit in favor of a more
thoroughly thought-through solution.  The new theory is that a PHV's
ph_eval_at level cannot be higher than its original syntactic level.  If it
contains lateral references, those don't change the ph_eval_at level, but
rather they create a lateral-reference requirement for the ph_eval_at join
relation.  The code in joinpath.c needs to handle that.

Another issue is that createplan.c wasn't handling nested PlaceHolderVars
properly.

In passing, push knowledge of lateral-reference checks for join clauses
into join_clause_is_movable_to.  This is mainly so that FDWs don't need
to deal with it.

This patch doesn't fix the original join-qual-placement problem reported by
Jeremy Evans (and indeed, one of the new regression test cases shows the
wrong answer because of that).  But the PlaceHolderVar problems need to be
fixed before that issue can be addressed, so committing this separately
seems reasonable.

1 files changed, 18 insertions, 5 deletions

diff --git a/src/backend/optimizer/util/restrictinfo.c b/src/backend/optimizer/util/restrictinfo.c
index cd7109b687b..33b029b0e4e 100644
--- a/src/backend/optimizer/util/restrictinfo.c
+++ b/src/backend/optimizer/util/restrictinfo.c
@@ -651,24 +651,32 @@ extract_actual_join_clauses(List *restrictinfo_list,
  * outer join, as that would change the results (rows would be suppressed
  * rather than being null-extended).
  *
- * And the target relation must not be in the clause's nullable_relids, i.e.,
+ * Also the target relation must not be in the clause's nullable_relids, i.e.,
  * there must not be an outer join below the clause that would null the Vars
  * coming from the target relation.  Otherwise the clause might give results
  * different from what it would give at its normal semantic level.
+ *
+ * Also, the join clause must not use any relations that have LATERAL
+ * references to the target relation, since we could not put such rels on
+ * the outer side of a nestloop with the target relation.
  */
 bool
-join_clause_is_movable_to(RestrictInfo *rinfo, Index baserelid)
+join_clause_is_movable_to(RestrictInfo *rinfo, RelOptInfo *baserel)
 {
 	/* Clause must physically reference target rel */
-	if (!bms_is_member(baserelid, rinfo->clause_relids))
+	if (!bms_is_member(baserel->relid, rinfo->clause_relids))
 		return false;
 
 	/* Cannot move an outer-join clause into the join's outer side */
-	if (bms_is_member(baserelid, rinfo->outer_relids))
+	if (bms_is_member(baserel->relid, rinfo->outer_relids))
 		return false;
 
 	/* Target rel must not be nullable below the clause */
-	if (bms_is_member(baserelid, rinfo->nullable_relids))
+	if (bms_is_member(baserel->relid, rinfo->nullable_relids))
+		return false;
+
+	/* Clause must not use any rels with LATERAL references to this rel */
+	if (bms_overlap(baserel->lateral_referencers, rinfo->clause_relids))
 		return false;
 
 	return true;
@@ -695,6 +703,11 @@ join_clause_is_movable_to(RestrictInfo *rinfo, Index baserelid)
  * not pushing the clause into its outer-join outer side, nor down into
  * a lower outer join's inner side.
  *
+ * There's no check here equivalent to join_clause_is_movable_to's test on
+ * lateral_relids.	We assume the caller wouldn't be inquiring unless it'd
+ * verified that the proposed outer rels don't have lateral references to
+ * the current rel(s).
+ *
  * Note: get_joinrel_parampathinfo depends on the fact that if
  * current_and_outer is NULL, this function will always return false
  * (since one or the other of the first two tests must fail).