d25ea01275-fixup-v13.patch

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index db3a68a51d..70d0be691e 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1067,7 +1067,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		 * paths that produce those sort orderings).
 		 */
 		if (rel->has_eclass_joins || has_useful_pathkeys(root, rel))
-			add_child_rel_equivalences(root, appinfo, rel, childrel);
+			add_child_rel_equivalences(root, &appinfo, 1, rel, childrel);
 		childrel->has_eclass_joins = rel->has_eclass_joins;
 
 		/*
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index ccc07ba9f0..67756ab671 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1193,6 +1193,20 @@ generate_join_implied_equalities(PlannerInfo *root,
 		result = list_concat(result, sublist);
 	}
 
+	/*
+	 * Remember the EC indexes that were found to refer to this joinrel.  This
+	 * is useful in add_child_rel_equivalences() where we need to add child
+	 * versions of the expressions in each of these ECs.
+	 */
+	if (!bms_is_empty(matching_ecs))
+	{
+		RelOptInfo *joinrel = find_join_rel(root, join_relids);
+
+		if (joinrel)
+			joinrel->eclass_indexes = bms_add_members(joinrel->eclass_indexes,
+													  matching_ecs);
+	}
+
 	return result;
 }
 
@@ -2215,12 +2229,15 @@ match_eclasses_to_foreign_key_col(PlannerInfo *root,
  * Note that this function won't be called at all unless we have at least some
  * reason to believe that the EC members it generates will be useful.
  *
- * parent_rel and child_rel could be derived from appinfo, but since the
+ * parent_rel and child_rel could be derived from appinfos, but since the
  * caller has already computed them, we might as well just pass them in.
+ * Note that parent_rel and child_rel are either BASEREL and OTHER_MEMBER_REL,
+ * respectively, or JOINREL and OTHER_JOINREL.
  */
 void
 add_child_rel_equivalences(PlannerInfo *root,
-						   AppendRelInfo *appinfo,
+						   AppendRelInfo **appinfos,
+						   int		nappinfos,
 						   RelOptInfo *parent_rel,
 						   RelOptInfo *child_rel)
 {
@@ -2231,13 +2248,14 @@ add_child_rel_equivalences(PlannerInfo *root,
 	 * eclass_indexes to avoid searching all of root->eq_classes.
 	 */
 	Assert(root->ec_merging_done);
-	Assert(IS_SIMPLE_REL(parent_rel));
+	Assert(IS_SIMPLE_REL(parent_rel) || IS_JOIN_REL(parent_rel));
 
 	i = -1;
 	while ((i = bms_next_member(parent_rel->eclass_indexes, i)) >= 0)
 	{
 		EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
 		int			num_members;
+		EquivalenceMember *first_em;
 
 		/*
 		 * If this EC contains a volatile expression, then generating child
@@ -2247,8 +2265,21 @@ add_child_rel_equivalences(PlannerInfo *root,
 		if (cur_ec->ec_has_volatile)
 			continue;
 
+		first_em = (EquivalenceMember *) linitial(cur_ec->ec_members);
+
+		/*
+		 * Only translate ECs whose members expressions could possibly match
+		 * the parent relation.  That is, for "baserel" parent and child
+		 * relations only consider ECs that contain single-relation members,
+		 * whereas, for "joinrel" parent and child relations, only consider ECs
+		 * that contain multi-relation members.  Note that looking at the first
+		 * EC member is enough, because others should look the same.
+		 */
+		if (bms_membership(parent_rel->relids) != bms_membership(first_em->em_relids))
+			continue;
+
 		/* Sanity check eclass_indexes only contain ECs for parent_rel */
-		Assert(bms_is_subset(child_rel->top_parent_relids, cur_ec->ec_relids));
+		Assert(bms_overlap(child_rel->top_parent_relids, cur_ec->ec_relids));
 
 		/*
 		 * We don't use foreach() here because there's no point in scanning
@@ -2263,34 +2294,31 @@ add_child_rel_equivalences(PlannerInfo *root,
 			if (cur_em->em_is_const)
 				continue;		/* ignore consts here */
 
-			/*
-			 * We consider only original EC members here, not
-			 * already-transformed child members.  Otherwise, if some original
-			 * member expression references more than one appendrel, we'd get
-			 * an O(N^2) explosion of useless derived expressions for
-			 * combinations of children.
-			 */
-			if (cur_em->em_is_child)
-				continue;		/* ignore children here */
-
 			/* Does this member reference child's topmost parent rel? */
-			if (bms_overlap(cur_em->em_relids, child_rel->top_parent_relids))
+			if (bms_is_subset(cur_em->em_relids, child_rel->top_parent_relids))
 			{
 				/* Yes, generate transformed child version */
 				Expr	   *child_expr;
 				Relids		new_relids;
 				Relids		new_nullable_relids;
 
-				if (parent_rel->reloptkind == RELOPT_BASEREL)
+				/*
+				 * If the parent_rel is itself the topmost parent rel, transform
+				 * directly.
+				 */
+				if (parent_rel->reloptkind == RELOPT_BASEREL ||
+					parent_rel->reloptkind == RELOPT_JOINREL)
 				{
 					/* Simple single-level transformation */
 					child_expr = (Expr *)
 						adjust_appendrel_attrs(root,
 											   (Node *) cur_em->em_expr,
-											   1, &appinfo);
+											   nappinfos, appinfos);
 				}
 				else
 				{
+					Assert(parent_rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
+						   parent_rel->reloptkind == RELOPT_OTHER_JOINREL);
 					/* Must do multi-level transformation */
 					child_expr = (Expr *)
 						adjust_appendrel_attrs_multilevel(root,
@@ -2329,6 +2357,12 @@ add_child_rel_equivalences(PlannerInfo *root,
 
 				/* Record this EC index for the child rel */
 				child_rel->eclass_indexes = bms_add_member(child_rel->eclass_indexes, i);
+
+				/*
+				 * There aren't going to be more expressions to translate in
+				 * the same EC.
+				 */
+				break;
 			}
 		}
 	}
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 85415381fb..e659606e9e 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -661,6 +661,9 @@ build_join_rel(PlannerInfo *root,
 	joinrel->nullable_partexprs = NULL;
 	joinrel->partitioned_child_rels = NIL;
 
+	/* Add the joinrel to the PlannerInfo. */
+	add_join_rel(root, joinrel);
+
 	/* Compute information relevant to the foreign relations. */
 	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
 
@@ -734,9 +737,6 @@ build_join_rel(PlannerInfo *root,
 		is_parallel_safe(root, (Node *) joinrel->reltarget->exprs))
 		joinrel->consider_parallel = true;
 
-	/* Add the joinrel to the PlannerInfo. */
-	add_join_rel(root, joinrel);
-
 	/*
 	 * Also, if dynamic-programming join search is active, add the new joinrel
 	 * to the appropriate sublist.  Note: you might think the Assert on number
@@ -854,6 +854,16 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 														(Node *) parent_joinrel->joininfo,
 														nappinfos,
 														appinfos);
+
+	/*
+	 * If the parent joinrel has pending equivalence classes, so does the
+	 * child.
+	 */
+	if (parent_joinrel->has_eclass_joins ||
+		has_useful_pathkeys(root, parent_joinrel))
+			add_child_rel_equivalences(root, appinfos, nappinfos,
+									   parent_joinrel, joinrel);
+
 	pfree(appinfos);
 
 	/*
@@ -863,10 +873,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 	joinrel->direct_lateral_relids = (Relids) bms_copy(parent_joinrel->direct_lateral_relids);
 	joinrel->lateral_relids = (Relids) bms_copy(parent_joinrel->lateral_relids);
 
-	/*
-	 * If the parent joinrel has pending equivalence classes, so does the
-	 * child.
-	 */
 	joinrel->has_eclass_joins = parent_joinrel->has_eclass_joins;
 
 	/* Is the join between partitions itself partitioned? */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7345137d1d..832cc84bb4 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -150,7 +150,8 @@ extern EquivalenceClass *match_eclasses_to_foreign_key_col(PlannerInfo *root,
 														   ForeignKeyOptInfo *fkinfo,
 														   int colno);
 extern void add_child_rel_equivalences(PlannerInfo *root,
-									   AppendRelInfo *appinfo,
+									   AppendRelInfo **appinfos,
+									   int		nappinfos,
 									   RelOptInfo *parent_rel,
 									   RelOptInfo *child_rel);
 extern List *generate_implied_equalities_for_column(PlannerInfo *root,
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index c448d85dec..003c8d1c24 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -439,3 +439,67 @@ explain (costs off)
    Filter: ((unique1 = unique1) OR (unique2 = unique2))
 (2 rows)
 
+-- Check that child merge join for a FULL OUTER join works correctly
+SET enable_partitionwise_join TO on;
+SET enable_partitionwise_aggregate TO on;
+CREATE TABLE child_joins_ecs_testtab1 (a int);
+INSERT INTO child_joins_ecs_testtab1 SELECT generate_series(1, 100);
+CREATE TABLE child_joins_ecs_testtab2 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE child_joins_ecs_testtab2_p1 PARTITION OF child_joins_ecs_testtab2 FOR VALUES FROM (1) TO (10001);
+CREATE TABLE child_joins_ecs_testtab2_p2 PARTITION OF child_joins_ecs_testtab2 FOR VALUES FROM (10001) TO (20001);
+CREATE TABLE child_joins_ecs_testtab2_p3 PARTITION OF child_joins_ecs_testtab2 FOR VALUES FROM (20001) TO (30001);
+INSERT INTO child_joins_ecs_testtab2 SELECT a, a % 100 + 1 FROM generate_series(1, 30000) a;
+ANALYZE child_joins_ecs_testtab1, child_joins_ecs_testtab2;
+-- this forces plan to be a specific shape
+SET work_mem TO '0.1MB';
+SET max_parallel_workers_per_gather TO 0;
+EXPLAIN (COSTS OFF)
+SELECT child_joins_ecs_testtab1.*
+  FROM (SELECT a, b
+        FROM child_joins_ecs_testtab2 t1 FULL JOIN child_joins_ecs_testtab2 t2 USING(a, b)
+        WHERE a >= 1 AND a < 200000
+        GROUP BY 1, 2) AS data
+  JOIN child_joins_ecs_testtab1 ON (child_joins_ecs_testtab1.a = data.b);
+                                                    QUERY PLAN                                                     
+-------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((COALESCE(t1.b, t2.b)) = child_joins_ecs_testtab1.a)
+   ->  Group
+         Group Key: (COALESCE(t1.a, t2.a)), (COALESCE(t1.b, t2.b))
+         ->  Merge Append
+               Sort Key: (COALESCE(t1.a, t2.a)), (COALESCE(t1.b, t2.b))
+               ->  Group
+                     Group Key: (COALESCE(t1.a, t2.a)), (COALESCE(t1.b, t2.b))
+                     ->  Sort
+                           Sort Key: (COALESCE(t1.a, t2.a)), (COALESCE(t1.b, t2.b))
+                           ->  Hash Full Join
+                                 Hash Cond: ((t1.a = t2.a) AND (t1.b = t2.b))
+                                 Filter: ((COALESCE(t1.a, t2.a) >= 1) AND (COALESCE(t1.a, t2.a) < 200000))
+                                 ->  Seq Scan on child_joins_ecs_testtab2_p1 t1
+                                 ->  Hash
+                                       ->  Seq Scan on child_joins_ecs_testtab2_p1 t2
+               ->  Group
+                     Group Key: (COALESCE(t1_1.a, t2_1.a)), (COALESCE(t1_1.b, t2_1.b))
+                     ->  Sort
+                           Sort Key: (COALESCE(t1_1.a, t2_1.a)), (COALESCE(t1_1.b, t2_1.b))
+                           ->  Hash Full Join
+                                 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b))
+                                 Filter: ((COALESCE(t1_1.a, t2_1.a) >= 1) AND (COALESCE(t1_1.a, t2_1.a) < 200000))
+                                 ->  Seq Scan on child_joins_ecs_testtab2_p2 t1_1
+                                 ->  Hash
+                                       ->  Seq Scan on child_joins_ecs_testtab2_p2 t2_1
+               ->  Group
+                     Group Key: (COALESCE(t1_2.a, t2_2.a)), (COALESCE(t1_2.b, t2_2.b))
+                     ->  Sort
+                           Sort Key: (COALESCE(t1_2.a, t2_2.a)), (COALESCE(t1_2.b, t2_2.b))
+                           ->  Hash Full Join
+                                 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b))
+                                 Filter: ((COALESCE(t1_2.a, t2_2.a) >= 1) AND (COALESCE(t1_2.a, t2_2.a) < 200000))
+                                 ->  Seq Scan on child_joins_ecs_testtab2_p3 t1_2
+                                 ->  Hash
+                                       ->  Seq Scan on child_joins_ecs_testtab2_p3 t2_2
+   ->  Materialize
+         ->  Seq Scan on child_joins_ecs_testtab1
+(38 rows)
+
+DROP TABLE child_joins_ecs_testtab1, child_joins_ecs_testtab2;
diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql
index 85aa65de39..bd792dfc3c 100644
--- a/src/test/regress/sql/equivclass.sql
+++ b/src/test/regress/sql/equivclass.sql
@@ -262,3 +262,26 @@ explain (costs off)
 -- this could be converted, but isn't at present
 explain (costs off)
   select * from tenk1 where unique1 = unique1 or unique2 = unique2;
+
+-- Check that child merge join for a FULL OUTER join works correctly
+SET enable_partitionwise_join TO on;
+SET enable_partitionwise_aggregate TO on;
+CREATE TABLE child_joins_ecs_testtab1 (a int);
+INSERT INTO child_joins_ecs_testtab1 SELECT generate_series(1, 100);
+CREATE TABLE child_joins_ecs_testtab2 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE child_joins_ecs_testtab2_p1 PARTITION OF child_joins_ecs_testtab2 FOR VALUES FROM (1) TO (10001);
+CREATE TABLE child_joins_ecs_testtab2_p2 PARTITION OF child_joins_ecs_testtab2 FOR VALUES FROM (10001) TO (20001);
+CREATE TABLE child_joins_ecs_testtab2_p3 PARTITION OF child_joins_ecs_testtab2 FOR VALUES FROM (20001) TO (30001);
+INSERT INTO child_joins_ecs_testtab2 SELECT a, a % 100 + 1 FROM generate_series(1, 30000) a;
+ANALYZE child_joins_ecs_testtab1, child_joins_ecs_testtab2;
+-- this forces plan to be a specific shape
+SET work_mem TO '0.1MB';
+SET max_parallel_workers_per_gather TO 0;
+EXPLAIN (COSTS OFF)
+SELECT child_joins_ecs_testtab1.*
+  FROM (SELECT a, b
+        FROM child_joins_ecs_testtab2 t1 FULL JOIN child_joins_ecs_testtab2 t2 USING(a, b)
+        WHERE a >= 1 AND a < 200000
+        GROUP BY 1, 2) AS data
+  JOIN child_joins_ecs_testtab1 ON (child_joins_ecs_testtab1.a = data.b);
+DROP TABLE child_joins_ecs_testtab1, child_joins_ecs_testtab2;