Hi,
Here's a much more polished version of the patches, with per-branch
patches. I've significantly shortened the commit message, expanded the
tests a bit to test additional variations, etc.
The 16-master code is the same, there are just some minor plan changes
in tests. The 15 is more different, as it does not have the JoinDomain
stuff, so it needs to fix just one of the bugs.
After all this cleanup and more testing, I feel much more confident in
correctness of the patches. It's not a very complex fix, so I intend to
commit not to long after the minor releases are out.
I'd still welcome a review and feedback, of course. One thing I'm not
entirely sure about is the lookup of SpecialJoinInfo in the fix for 15
(in reconsider_full_join_clause).
I'm wondering about these things:
1) Is there a better way to find the right join for the clause than
walking root->join_info_list? I haven't found any. In 16 and later
reconsider_full_join_clause gets it from the outside through the
OuterJoinClauseInfo. I believe that's not possible in 15.
2) Let's say we need to do the lookup in root->join_info_list. Is the
condition used to "match" the join correct?
bms_equal(rinfo->outer_relids,
bms_union(cur_sjinfo->syn_lefthand,
cur_sjinfo->syn_righthand)))
Or could it be wrong in some corner cases? AFAIK it's correct, but maybe
there's a weird corner case that breaks it.
3) I'm not sure about the elog(ERROR) after the lookup:
if (sjinfo == NULL)
elog(ERROR, "failed to find SpecialJoinInfo for FULL JOIN clause");
Maybe it should be replaced with an assert - we're not really expecting
a failure here, right? We should always find the SpecialJoinInfo. But if
the condition in (2) may not find the right join, maybe it'd be better
to return false here, as if the condition can't be propagated. Otherwise
a production build would segfault on the next block that dereferences
the sjinfo pointer.
So I'm thinking about replacing it with something like
/* failed to find SpecialJoinInfo for FULL JOIN clause */
if (sjinfo == NULL)
{
Assert(false);
return false;
}
Suggestions welcome, ofc.
regards
--
Tomas Vondra
From ce1c9ec8908ae38a137e3ba26497f02a00d922fd Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 7 Nov 2026 21:27:50 +0100
Subject: [PATCH v2-master] Fix constant propagation through FULL JOIN clauses
Given a FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass
containing COALESCE(LEFTVAR, RIGHTVAR) and a constant,
reconsider_full_join_clause() derives "LEFTVAR = CONSTANT" and "RIGHTVAR
= CONSTANT" and discards the join clause and the COALESCE condition. It
assumed LEFTVAR comes from the join's lefthand input and RIGHTVAR from
its righthand input, which holds for FULL JOIN USING but not necessarily
for a user-written ON clause.
With the operands reversed, the derived equalities got the wrong
JoinDomains. That could cause an assertion failure or, in non-assert
builds, incorrect results, due to a derived qual being applied to the
wrong side of the join. With both operands from the same input, or one
referencing both inputs, nothing can be deduced, yet bogus equalities
were derived anyway. Both could produce wrong results.
Fix by determining which input each operand comes from, and giving up
unless they come from different inputs. A FULL JOIN whose only condition
is a clause of the latter kind now fails with "FULL JOIN is only
supported with merge-joinable or hash-joinable join conditions", as it
would without the COALESCE condition.
The JoinDomain problem was introduced in v16. The other one (bogus
equalities derived from a qual with both operands from the same input,
or one referencing both inputs) exists in all supported branches, but
before outer-join-aware Vars (v16) only an operand referencing both
inputs can trigger it.
Backpatch to all supported versions. The 15 branch needs only the part
addressing the second issue.
Author: Tomas Vondra <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
src/backend/optimizer/path/equivclass.c | 36 +++-
src/backend/optimizer/plan/initsplan.c | 7 +-
src/test/regress/expected/join.out | 211 ++++++++++++++++++++++++
src/test/regress/sql/join.sql | 83 ++++++++++
4 files changed, 326 insertions(+), 11 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 598b62b6764..439cb651c01 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2444,6 +2444,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
right_type;
Relids left_relids,
right_relids;
+ JoinDomain *left_jdomain,
+ *right_jdomain;
ListCell *lc1;
/* Extract needed info from the clause */
@@ -2456,6 +2458,29 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
left_relids = rinfo->left_relids;
right_relids = rinfo->right_relids;
+ /*
+ * Identify the child JoinDomains within which LEFTVAR = CONSTANT and
+ * RIGHTVAR = CONSTANT would hold. We mustn't assume that LEFTVAR comes
+ * from the join's lefthand input, since the clause might have been
+ * written as, say, "a FULL JOIN b ON b.y = a.x". If the clause doesn't
+ * relate an expression of one input to an expression of the other, we
+ * can't deduce anything from it.
+ */
+ if (bms_is_subset(left_relids, sjinfo->syn_lefthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_righthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ }
+ else if (bms_is_subset(left_relids, sjinfo->syn_righthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_lefthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ }
+ else
+ return false;
+
foreach(lc1, root->eq_classes)
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
@@ -2547,7 +2572,6 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
Oid eq_op;
RestrictInfo *newrinfo;
- JoinDomain *jdomain;
if (!cur_em->em_is_const)
continue; /* ignore non-const members */
@@ -2563,9 +2587,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(left_relids),
cur_ec->ec_min_security);
- /* This equality holds within the lefthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_lefthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within LEFTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, left_jdomain))
matchleft = true;
}
eq_op = select_equality_operator(cur_ec,
@@ -2580,9 +2603,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(right_relids),
cur_ec->ec_min_security);
- /* This equality holds within the righthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_righthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within RIGHTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, right_jdomain))
matchright = true;
}
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index cec76342f6e..9f8d51eb372 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -3176,10 +3176,9 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
*
* If it is a full outer-join qualification, we add it to
* root->full_join_clauses. (Ideally we'd discard cases that aren't
- * leftvar = rightvar, as we do for left/right joins, but this routine
- * doesn't have the info needed to do that; and the current usage of the
- * full_join_clauses list doesn't require that, so it's not currently
- * worth complicating this routine's API to make it possible.)
+ * leftvar = rightvar, as we do for left/right joins, but we leave that to
+ * reconsider_full_join_clause(), which has to work out which side of the
+ * join each operand comes from anyway.)
*
* If none of the above hold, pass it off to
* distribute_restrictinfo_to_rels().
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 7773852e61b..3100f084cd5 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6556,6 +6556,217 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
(10 rows)
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(6 rows)
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1
+----+----
+ 0 | 0
+(1 row)
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(9 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+-------------------------------------------------------
+ Merge Full Join
+ InitPlan expr_1
+ -> Result
+ -> Result
+ One-Time Filter: ((InitPlan expr_1).col1 = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(13 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ | | 0
+(1 row)
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ QUERY PLAN
+------------------------------------------------
+ Sort
+ Sort Key: b.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: (a.f1 = b.f1)
+ Filter: (COALESCE(a.f1, b.f1) = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ f1 | f1 | f1
+----+-------------+----
+ 0 | -2147483647 |
+ 0 | -123456 |
+ 0 | 0 | 0
+ 0 | 123456 |
+ 0 | 2147483647 |
+(5 rows)
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: a.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: ((a.f1 - c.f1) = b.f1)
+ Filter: (COALESCE((a.f1 - c.f1), b.f1) = 123456)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ f1 | f1 | f1
+-------------+--------+----
+ -2147483647 | 123456 |
+ -123456 | 123456 |
+ 0 | 123456 |
+ 123456 | 123456 |
+ 2147483647 | 123456 |
+(5 rows)
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
--
-- check handling of join aliases when flattening multiple levels of subquery
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index a49d343ec32..e37a002030f 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2278,6 +2278,89 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+
--
-- check handling of join aliases when flattening multiple levels of subquery
--
--
2.55.0
From b2d49688fe84032b9807e0505f56246f9d803467 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 7 Nov 2026 21:27:50 +0100
Subject: [PATCH v2-19] Fix constant propagation through FULL JOIN clauses
Given a FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass
containing COALESCE(LEFTVAR, RIGHTVAR) and a constant,
reconsider_full_join_clause() derives "LEFTVAR = CONSTANT" and "RIGHTVAR
= CONSTANT" and discards the join clause and the COALESCE condition. It
assumed LEFTVAR comes from the join's lefthand input and RIGHTVAR from
its righthand input, which holds for FULL JOIN USING but not necessarily
for a user-written ON clause.
With the operands reversed, the derived equalities got the wrong
JoinDomains. That could cause an assertion failure or, in non-assert
builds, incorrect results, due to a derived qual being applied to the
wrong side of the join. With both operands from the same input, or one
referencing both inputs, nothing can be deduced, yet bogus equalities
were derived anyway. Both could produce wrong results.
Fix by determining which input each operand comes from, and giving up
unless they come from different inputs. A FULL JOIN whose only condition
is a clause of the latter kind now fails with "FULL JOIN is only
supported with merge-joinable or hash-joinable join conditions", as it
would without the COALESCE condition.
The JoinDomain problem was introduced in v16. The other one (bogus
equalities derived from a qual with both operands from the same input,
or one referencing both inputs) exists in all supported branches, but
before outer-join-aware Vars (v16) only an operand referencing both
inputs can trigger it.
Backpatch to all supported versions. The 15 branch needs only the part
addressing the second issue.
Author: Tomas Vondra <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
src/backend/optimizer/path/equivclass.c | 36 +++-
src/backend/optimizer/plan/initsplan.c | 7 +-
src/test/regress/expected/join.out | 211 ++++++++++++++++++++++++
src/test/regress/sql/join.sql | 83 ++++++++++
4 files changed, 326 insertions(+), 11 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index fe1786ed61a..f53a2341c55 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2445,6 +2445,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
right_type;
Relids left_relids,
right_relids;
+ JoinDomain *left_jdomain,
+ *right_jdomain;
ListCell *lc1;
/* Extract needed info from the clause */
@@ -2457,6 +2459,29 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
left_relids = rinfo->left_relids;
right_relids = rinfo->right_relids;
+ /*
+ * Identify the child JoinDomains within which LEFTVAR = CONSTANT and
+ * RIGHTVAR = CONSTANT would hold. We mustn't assume that LEFTVAR comes
+ * from the join's lefthand input, since the clause might have been
+ * written as, say, "a FULL JOIN b ON b.y = a.x". If the clause doesn't
+ * relate an expression of one input to an expression of the other, we
+ * can't deduce anything from it.
+ */
+ if (bms_is_subset(left_relids, sjinfo->syn_lefthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_righthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ }
+ else if (bms_is_subset(left_relids, sjinfo->syn_righthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_lefthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ }
+ else
+ return false;
+
foreach(lc1, root->eq_classes)
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
@@ -2548,7 +2573,6 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
Oid eq_op;
RestrictInfo *newrinfo;
- JoinDomain *jdomain;
if (!cur_em->em_is_const)
continue; /* ignore non-const members */
@@ -2564,9 +2588,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(left_relids),
cur_ec->ec_min_security);
- /* This equality holds within the lefthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_lefthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within LEFTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, left_jdomain))
matchleft = true;
}
eq_op = select_equality_operator(cur_ec,
@@ -2581,9 +2604,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(right_relids),
cur_ec->ec_min_security);
- /* This equality holds within the righthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_righthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within RIGHTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, right_jdomain))
matchright = true;
}
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 78d708a7041..25e0e6ee106 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -3164,10 +3164,9 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
*
* If it is a full outer-join qualification, we add it to
* root->full_join_clauses. (Ideally we'd discard cases that aren't
- * leftvar = rightvar, as we do for left/right joins, but this routine
- * doesn't have the info needed to do that; and the current usage of the
- * full_join_clauses list doesn't require that, so it's not currently
- * worth complicating this routine's API to make it possible.)
+ * leftvar = rightvar, as we do for left/right joins, but we leave that to
+ * reconsider_full_join_clause(), which has to work out which side of the
+ * join each operand comes from anyway.)
*
* If none of the above hold, pass it off to
* distribute_restrictinfo_to_rels().
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index bc0d4ca8a71..a6a49c823ae 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5783,6 +5783,217 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
(10 rows)
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(6 rows)
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1
+----+----
+ 0 | 0
+(1 row)
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(9 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+-------------------------------------------------------
+ Merge Full Join
+ InitPlan expr_1
+ -> Result
+ -> Result
+ One-Time Filter: ((InitPlan expr_1).col1 = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(13 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ | | 0
+(1 row)
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ QUERY PLAN
+------------------------------------------------
+ Sort
+ Sort Key: b.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: (a.f1 = b.f1)
+ Filter: (COALESCE(a.f1, b.f1) = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ f1 | f1 | f1
+----+-------------+----
+ 0 | -2147483647 |
+ 0 | -123456 |
+ 0 | 0 | 0
+ 0 | 123456 |
+ 0 | 2147483647 |
+(5 rows)
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: a.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: ((a.f1 - c.f1) = b.f1)
+ Filter: (COALESCE((a.f1 - c.f1), b.f1) = 123456)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ f1 | f1 | f1
+-------------+--------+----
+ -2147483647 | 123456 |
+ -123456 | 123456 |
+ 0 | 123456 |
+ 123456 | 123456 |
+ 2147483647 | 123456 |
+(5 rows)
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
--
-- check handling of join aliases when flattening multiple levels of subquery
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 1f9895d8e87..f520fdae997 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1989,6 +1989,89 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+
--
-- check handling of join aliases when flattening multiple levels of subquery
--
--
2.55.0
From a1a7f1a6a8a07bc2fb3d09156b4a6afd88a38847 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 7 Nov 2026 21:27:50 +0100
Subject: [PATCH v2-18] Fix constant propagation through FULL JOIN clauses
Given a FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass
containing COALESCE(LEFTVAR, RIGHTVAR) and a constant,
reconsider_full_join_clause() derives "LEFTVAR = CONSTANT" and "RIGHTVAR
= CONSTANT" and discards the join clause and the COALESCE condition. It
assumed LEFTVAR comes from the join's lefthand input and RIGHTVAR from
its righthand input, which holds for FULL JOIN USING but not necessarily
for a user-written ON clause.
With the operands reversed, the derived equalities got the wrong
JoinDomains. That could cause an assertion failure or, in non-assert
builds, incorrect results, due to a derived qual being applied to the
wrong side of the join. With both operands from the same input, or one
referencing both inputs, nothing can be deduced, yet bogus equalities
were derived anyway. Both could produce wrong results.
Fix by determining which input each operand comes from, and giving up
unless they come from different inputs. A FULL JOIN whose only condition
is a clause of the latter kind now fails with "FULL JOIN is only
supported with merge-joinable or hash-joinable join conditions", as it
would without the COALESCE condition.
The JoinDomain problem was introduced in v16. The other one (bogus
equalities derived from a qual with both operands from the same input,
or one referencing both inputs) exists in all supported branches, but
before outer-join-aware Vars (v16) only an operand referencing both
inputs can trigger it.
Backpatch to all supported versions. The 15 branch needs only the part
addressing the second issue.
Author: Tomas Vondra <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
src/backend/optimizer/path/equivclass.c | 36 +++-
src/backend/optimizer/plan/initsplan.c | 7 +-
src/test/regress/expected/join.out | 211 ++++++++++++++++++++++++
src/test/regress/sql/join.sql | 83 ++++++++++
4 files changed, 326 insertions(+), 11 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 53905e5f784..94e1d86c8a2 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2431,6 +2431,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
right_type;
Relids left_relids,
right_relids;
+ JoinDomain *left_jdomain,
+ *right_jdomain;
ListCell *lc1;
/* Extract needed info from the clause */
@@ -2443,6 +2445,29 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
left_relids = rinfo->left_relids;
right_relids = rinfo->right_relids;
+ /*
+ * Identify the child JoinDomains within which LEFTVAR = CONSTANT and
+ * RIGHTVAR = CONSTANT would hold. We mustn't assume that LEFTVAR comes
+ * from the join's lefthand input, since the clause might have been
+ * written as, say, "a FULL JOIN b ON b.y = a.x". If the clause doesn't
+ * relate an expression of one input to an expression of the other, we
+ * can't deduce anything from it.
+ */
+ if (bms_is_subset(left_relids, sjinfo->syn_lefthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_righthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ }
+ else if (bms_is_subset(left_relids, sjinfo->syn_righthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_lefthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ }
+ else
+ return false;
+
foreach(lc1, root->eq_classes)
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
@@ -2534,7 +2559,6 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
Oid eq_op;
RestrictInfo *newrinfo;
- JoinDomain *jdomain;
if (!cur_em->em_is_const)
continue; /* ignore non-const members */
@@ -2550,9 +2574,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(left_relids),
cur_ec->ec_min_security);
- /* This equality holds within the lefthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_lefthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within LEFTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, left_jdomain))
matchleft = true;
}
eq_op = select_equality_operator(cur_ec,
@@ -2567,9 +2590,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(right_relids),
cur_ec->ec_min_security);
- /* This equality holds within the righthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_righthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within RIGHTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, right_jdomain))
matchright = true;
}
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 642a05c36d8..7afc1a94631 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2883,10 +2883,9 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
*
* If it is a full outer-join qualification, we add it to
* root->full_join_clauses. (Ideally we'd discard cases that aren't
- * leftvar = rightvar, as we do for left/right joins, but this routine
- * doesn't have the info needed to do that; and the current usage of the
- * full_join_clauses list doesn't require that, so it's not currently
- * worth complicating this routine's API to make it possible.)
+ * leftvar = rightvar, as we do for left/right joins, but we leave that to
+ * reconsider_full_join_clause(), which has to work out which side of the
+ * join each operand comes from anyway.)
*
* If none of the above hold, pass it off to
* distribute_restrictinfo_to_rels().
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 50d6a7d1504..be055a23124 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5687,6 +5687,217 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
(10 rows)
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(6 rows)
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1
+----+----
+ 0 | 0
+(1 row)
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(9 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+--------------------------------------------------
+ Merge Full Join
+ InitPlan 1
+ -> Result
+ -> Result
+ One-Time Filter: ((InitPlan 1).col1 = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(13 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ | | 0
+(1 row)
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ QUERY PLAN
+------------------------------------------------
+ Sort
+ Sort Key: b.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: (a.f1 = b.f1)
+ Filter: (COALESCE(a.f1, b.f1) = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ f1 | f1 | f1
+----+-------------+----
+ 0 | -2147483647 |
+ 0 | -123456 |
+ 0 | 0 | 0
+ 0 | 123456 |
+ 0 | 2147483647 |
+(5 rows)
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: a.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: ((a.f1 - c.f1) = b.f1)
+ Filter: (COALESCE((a.f1 - c.f1), b.f1) = 123456)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ f1 | f1 | f1
+-------------+--------+----
+ -2147483647 | 123456 |
+ -123456 | 123456 |
+ 0 | 123456 |
+ 123456 | 123456 |
+ 2147483647 | 123456 |
+(5 rows)
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
--
-- check handling of join aliases when flattening multiple levels of subquery
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 4932834ac9d..f17c2c5c627 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1950,6 +1950,89 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+
--
-- check handling of join aliases when flattening multiple levels of subquery
--
--
2.55.0
From 67b2989a30804185f8c96d94b04db283d50a1982 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 7 Nov 2026 21:27:50 +0100
Subject: [PATCH v2-17] Fix constant propagation through FULL JOIN clauses
Given a FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass
containing COALESCE(LEFTVAR, RIGHTVAR) and a constant,
reconsider_full_join_clause() derives "LEFTVAR = CONSTANT" and "RIGHTVAR
= CONSTANT" and discards the join clause and the COALESCE condition. It
assumed LEFTVAR comes from the join's lefthand input and RIGHTVAR from
its righthand input, which holds for FULL JOIN USING but not necessarily
for a user-written ON clause.
With the operands reversed, the derived equalities got the wrong
JoinDomains. That could cause an assertion failure or, in non-assert
builds, incorrect results, due to a derived qual being applied to the
wrong side of the join. With both operands from the same input, or one
referencing both inputs, nothing can be deduced, yet bogus equalities
were derived anyway. Both could produce wrong results.
Fix by determining which input each operand comes from, and giving up
unless they come from different inputs. A FULL JOIN whose only condition
is a clause of the latter kind now fails with "FULL JOIN is only
supported with merge-joinable or hash-joinable join conditions", as it
would without the COALESCE condition.
The JoinDomain problem was introduced in v16. The other one (bogus
equalities derived from a qual with both operands from the same input,
or one referencing both inputs) exists in all supported branches, but
before outer-join-aware Vars (v16) only an operand referencing both
inputs can trigger it.
Backpatch to all supported versions. The 15 branch needs only the part
addressing the second issue.
Author: Tomas Vondra <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
src/backend/optimizer/path/equivclass.c | 36 +++-
src/backend/optimizer/plan/initsplan.c | 7 +-
src/test/regress/expected/join.out | 211 ++++++++++++++++++++++++
src/test/regress/sql/join.sql | 83 ++++++++++
4 files changed, 326 insertions(+), 11 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 38beb4fe9c4..74b7b284943 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2284,6 +2284,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
right_type;
Relids left_relids,
right_relids;
+ JoinDomain *left_jdomain,
+ *right_jdomain;
ListCell *lc1;
/* Extract needed info from the clause */
@@ -2296,6 +2298,29 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
left_relids = rinfo->left_relids;
right_relids = rinfo->right_relids;
+ /*
+ * Identify the child JoinDomains within which LEFTVAR = CONSTANT and
+ * RIGHTVAR = CONSTANT would hold. We mustn't assume that LEFTVAR comes
+ * from the join's lefthand input, since the clause might have been
+ * written as, say, "a FULL JOIN b ON b.y = a.x". If the clause doesn't
+ * relate an expression of one input to an expression of the other, we
+ * can't deduce anything from it.
+ */
+ if (bms_is_subset(left_relids, sjinfo->syn_lefthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_righthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ }
+ else if (bms_is_subset(left_relids, sjinfo->syn_righthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_lefthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ }
+ else
+ return false;
+
foreach(lc1, root->eq_classes)
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
@@ -2382,7 +2407,6 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
Oid eq_op;
RestrictInfo *newrinfo;
- JoinDomain *jdomain;
if (!cur_em->em_is_const)
continue; /* ignore non-const members */
@@ -2398,9 +2422,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(left_relids),
cur_ec->ec_min_security);
- /* This equality holds within the lefthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_lefthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within LEFTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, left_jdomain))
matchleft = true;
}
eq_op = select_equality_operator(cur_ec,
@@ -2415,9 +2438,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(right_relids),
cur_ec->ec_min_security);
- /* This equality holds within the righthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_righthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within RIGHTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, right_jdomain))
matchright = true;
}
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 59a59064c54..c92ff869ec6 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2481,10 +2481,9 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
*
* If it is a full outer-join qualification, we add it to
* root->full_join_clauses. (Ideally we'd discard cases that aren't
- * leftvar = rightvar, as we do for left/right joins, but this routine
- * doesn't have the info needed to do that; and the current usage of the
- * full_join_clauses list doesn't require that, so it's not currently
- * worth complicating this routine's API to make it possible.)
+ * leftvar = rightvar, as we do for left/right joins, but we leave that to
+ * reconsider_full_join_clause(), which has to work out which side of the
+ * join each operand comes from anyway.)
*
* If none of the above hold, pass it off to
* distribute_restrictinfo_to_rels().
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index b734bac3d49..b5246387015 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5185,6 +5185,217 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
(10 rows)
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(6 rows)
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1
+----+----
+ 0 | 0
+(1 row)
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(9 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+--------------------------------------------------
+ Merge Full Join
+ InitPlan 1
+ -> Result
+ -> Result
+ One-Time Filter: ((InitPlan 1).col1 = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(13 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ | | 0
+(1 row)
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ QUERY PLAN
+------------------------------------------------
+ Sort
+ Sort Key: b.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: (a.f1 = b.f1)
+ Filter: (COALESCE(a.f1, b.f1) = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ f1 | f1 | f1
+----+-------------+----
+ 0 | -2147483647 |
+ 0 | -123456 |
+ 0 | 0 | 0
+ 0 | 123456 |
+ 0 | 2147483647 |
+(5 rows)
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: a.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: ((a.f1 - c.f1) = b.f1)
+ Filter: (COALESCE((a.f1 - c.f1), b.f1) = 123456)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ f1 | f1 | f1
+-------------+--------+----
+ -2147483647 | 123456 |
+ -123456 | 123456 |
+ 0 | 123456 |
+ 123456 | 123456 |
+ 2147483647 | 123456 |
+(5 rows)
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
--
-- check handling of join aliases when flattening multiple levels of subquery
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 26764c6a7bc..54945a78e8c 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1771,6 +1771,89 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+
--
-- check handling of join aliases when flattening multiple levels of subquery
--
--
2.55.0
From 7fbef5ee25560f4bcadde39840904ff0f30068ea Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 7 Nov 2026 21:27:50 +0100
Subject: [PATCH v2-15] Fix constant propagation through FULL JOIN clauses
Given a FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass
containing COALESCE(LEFTVAR, RIGHTVAR) and a constant,
reconsider_full_join_clause() derives "LEFTVAR = CONSTANT" and "RIGHTVAR
= CONSTANT" and discards the join clause and the COALESCE condition. It
assumed LEFTVAR comes from the join's lefthand input and RIGHTVAR from
its righthand input, which holds for FULL JOIN USING but not necessarily
for a user-written ON clause.
With the operands reversed, the derived equalities got the wrong
JoinDomains. That could cause an assertion failure or, in non-assert
builds, incorrect results, due to a derived qual being applied to the
wrong side of the join. With both operands from the same input, or one
referencing both inputs, nothing can be deduced, yet bogus equalities
were derived anyway. Both could produce wrong results.
Fix by determining which input each operand comes from, and giving up
unless they come from different inputs. A FULL JOIN whose only condition
is a clause of the latter kind now fails with "FULL JOIN is only
supported with merge-joinable or hash-joinable join conditions", as it
would without the COALESCE condition.
The JoinDomain problem was introduced in v16. The other one (bogus
equalities derived from a qual with both operands from the same input,
or one referencing both inputs) exists in all supported branches, but
before outer-join-aware Vars (v16) only an operand referencing both
inputs can trigger it.
Backpatch to all supported versions. The 15 branch needs only the part
addressing the second issue.
Author: Tomas Vondra <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
src/backend/optimizer/path/equivclass.c | 37 +++++++++++++
src/backend/optimizer/plan/initsplan.c | 6 +--
src/test/regress/expected/join.out | 69 +++++++++++++++++++++++++
src/test/regress/sql/join.sql | 33 ++++++++++++
4 files changed, 142 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 0341cf03663..3359a668881 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2200,6 +2200,7 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo)
right_relids,
left_nullable_relids,
right_nullable_relids;
+ SpecialJoinInfo *sjinfo = NULL;
ListCell *lc1;
/* Can't use an outerjoin_delayed clause here */
@@ -2220,6 +2221,42 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo)
right_nullable_relids = bms_intersect(right_relids,
rinfo->nullable_relids);
+ /*
+ * Find the SpecialJoinInfo of the FULL JOIN the clause belongs to. The
+ * outer_relids of a FULL JOIN clause are all the base rels syntactically
+ * within the join (see distribute_qual_to_rels), which identifies the
+ * join uniquely.
+ */
+ foreach(lc1, root->join_info_list)
+ {
+ SpecialJoinInfo *cur_sjinfo = (SpecialJoinInfo *) lfirst(lc1);
+
+ if (cur_sjinfo->jointype == JOIN_FULL &&
+ bms_equal(rinfo->outer_relids,
+ bms_union(cur_sjinfo->syn_lefthand,
+ cur_sjinfo->syn_righthand)))
+ {
+ sjinfo = cur_sjinfo;
+ break;
+ }
+ }
+ if (sjinfo == NULL)
+ elog(ERROR, "failed to find SpecialJoinInfo for FULL JOIN clause");
+
+ /*
+ * We can't deduce anything from the clause unless it relates an
+ * expression of one input of the join to an expression of the other.
+ * The operands may appear in either order, though, since the clause
+ * might have been written as, say, "a FULL JOIN b ON b.y = a.x". That
+ * doesn't matter below, as the derived clauses are placed according to
+ * their own relids.
+ */
+ if (!(bms_is_subset(left_relids, sjinfo->syn_lefthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_righthand)) &&
+ !(bms_is_subset(left_relids, sjinfo->syn_righthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_lefthand)))
+ return false;
+
foreach(lc1, root->eq_classes)
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 3fa3ef6bd41..6d6fe2b3988 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -1905,9 +1905,9 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
* If it is a full outer-join qualification, we add it to
* root->full_join_clauses. (Ideally we'd discard cases that aren't
* leftvar = rightvar, as we do for left/right joins, but this routine
- * doesn't have the info needed to do that; and the current usage of the
- * full_join_clauses list doesn't require that, so it's not currently
- * worth complicating this routine's API to make it possible.)
+ * doesn't have the info needed to do that, so we leave it to
+ * reconsider_full_join_clause(), which looks up the join's
+ * SpecialJoinInfo to work out which side each operand comes from.)
*
* If none of the above hold, pass it off to
* distribute_restrictinfo_to_rels().
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index f816d952c90..7c68e4bd2b6 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4046,6 +4046,75 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
---------+---------+---------+----------
(0 rows)
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+-- operands in reversed order: we can still deduce a.f1 = 0 and c.f1 = 0
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Hash
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(7 rows)
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1
+----+----
+ 0 | 0
+(1 row)
+
+-- nothing can be deduced from a clause with an operand that references both
+-- inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: a.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: ((a.f1 - c.f1) = b.f1)
+ Filter: (COALESCE((a.f1 - c.f1), b.f1) = 123456)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ f1 | f1 | f1
+-------------+--------+----
+ -2147483647 | 123456 |
+ -123456 | 123456 |
+ 0 | 123456 |
+ 123456 | 123456 |
+ 2147483647 | 123456 |
+(5 rows)
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
--
-- check handling of join aliases when flattening multiple levels of subquery
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 0fe9131cdec..affe1c4cbaf 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1354,6 +1354,39 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)
where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44;
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+
+-- operands in reversed order: we can still deduce a.f1 = 0 and c.f1 = 0
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- nothing can be deduced from a clause with an operand that references both
+-- inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+
--
-- check handling of join aliases when flattening multiple levels of subquery
--
--
2.55.0
From a72978d6dedd8ae598c2551814c3e202b894b215 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sat, 7 Nov 2026 21:27:50 +0100
Subject: [PATCH v2-16] Fix constant propagation through FULL JOIN clauses
Given a FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass
containing COALESCE(LEFTVAR, RIGHTVAR) and a constant,
reconsider_full_join_clause() derives "LEFTVAR = CONSTANT" and "RIGHTVAR
= CONSTANT" and discards the join clause and the COALESCE condition. It
assumed LEFTVAR comes from the join's lefthand input and RIGHTVAR from
its righthand input, which holds for FULL JOIN USING but not necessarily
for a user-written ON clause.
With the operands reversed, the derived equalities got the wrong
JoinDomains. That could cause an assertion failure or, in non-assert
builds, incorrect results, due to a derived qual being applied to the
wrong side of the join. With both operands from the same input, or one
referencing both inputs, nothing can be deduced, yet bogus equalities
were derived anyway. Both could produce wrong results.
Fix by determining which input each operand comes from, and giving up
unless they come from different inputs. A FULL JOIN whose only condition
is a clause of the latter kind now fails with "FULL JOIN is only
supported with merge-joinable or hash-joinable join conditions", as it
would without the COALESCE condition.
The JoinDomain problem was introduced in v16. The other one (bogus
equalities derived from a qual with both operands from the same input,
or one referencing both inputs) exists in all supported branches, but
before outer-join-aware Vars (v16) only an operand referencing both
inputs can trigger it.
Backpatch to all supported versions. The 15 branch needs only the part
addressing the second issue.
Author: Tomas Vondra <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
src/backend/optimizer/path/equivclass.c | 36 +++-
src/backend/optimizer/plan/initsplan.c | 7 +-
src/test/regress/expected/join.out | 211 ++++++++++++++++++++++++
src/test/regress/sql/join.sql | 83 ++++++++++
4 files changed, 326 insertions(+), 11 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index b78cc26ed68..dbb83f63d52 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2284,6 +2284,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
right_type;
Relids left_relids,
right_relids;
+ JoinDomain *left_jdomain,
+ *right_jdomain;
ListCell *lc1;
/* Extract needed info from the clause */
@@ -2296,6 +2298,29 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
left_relids = rinfo->left_relids;
right_relids = rinfo->right_relids;
+ /*
+ * Identify the child JoinDomains within which LEFTVAR = CONSTANT and
+ * RIGHTVAR = CONSTANT would hold. We mustn't assume that LEFTVAR comes
+ * from the join's lefthand input, since the clause might have been
+ * written as, say, "a FULL JOIN b ON b.y = a.x". If the clause doesn't
+ * relate an expression of one input to an expression of the other, we
+ * can't deduce anything from it.
+ */
+ if (bms_is_subset(left_relids, sjinfo->syn_lefthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_righthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ }
+ else if (bms_is_subset(left_relids, sjinfo->syn_righthand) &&
+ bms_is_subset(right_relids, sjinfo->syn_lefthand))
+ {
+ left_jdomain = find_join_domain(root, sjinfo->syn_righthand);
+ right_jdomain = find_join_domain(root, sjinfo->syn_lefthand);
+ }
+ else
+ return false;
+
foreach(lc1, root->eq_classes)
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
@@ -2382,7 +2407,6 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
Oid eq_op;
RestrictInfo *newrinfo;
- JoinDomain *jdomain;
if (!cur_em->em_is_const)
continue; /* ignore non-const members */
@@ -2398,9 +2422,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(left_relids),
cur_ec->ec_min_security);
- /* This equality holds within the lefthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_lefthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within LEFTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, left_jdomain))
matchleft = true;
}
eq_op = select_equality_operator(cur_ec,
@@ -2415,9 +2438,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
cur_em->em_expr,
bms_copy(right_relids),
cur_ec->ec_min_security);
- /* This equality holds within the righthand child JoinDomain */
- jdomain = find_join_domain(root, sjinfo->syn_righthand);
- if (process_equivalence(root, &newrinfo, jdomain))
+ /* This equality holds within RIGHTVAR's child JoinDomain */
+ if (process_equivalence(root, &newrinfo, right_jdomain))
matchright = true;
}
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index a80d4e1681e..55bc8090515 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2482,10 +2482,9 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
*
* If it is a full outer-join qualification, we add it to
* root->full_join_clauses. (Ideally we'd discard cases that aren't
- * leftvar = rightvar, as we do for left/right joins, but this routine
- * doesn't have the info needed to do that; and the current usage of the
- * full_join_clauses list doesn't require that, so it's not currently
- * worth complicating this routine's API to make it possible.)
+ * leftvar = rightvar, as we do for left/right joins, but we leave that to
+ * reconsider_full_join_clause(), which has to work out which side of the
+ * join each operand comes from anyway.)
*
* If none of the above hold, pass it off to
* distribute_restrictinfo_to_rels().
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 1082f3dc8a0..b94bb31c317 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5186,6 +5186,217 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
(10 rows)
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(6 rows)
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1
+----+----
+ 0 | 0
+(1 row)
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------
+ Merge Full Join
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(9 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ InitPlan 1 (returns $0)
+ -> Result
+ -> Result
+ One-Time Filter: ($0 = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Seq Scan on int4_tbl b
+ Filter: (f1 = 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(13 rows)
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ | | 0
+(1 row)
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ QUERY PLAN
+------------------------------------------
+ Merge Full Join
+ -> Hash Left Join
+ Hash Cond: (a.f1 = b.f1)
+ Filter: (b.f1 = 0)
+ -> Seq Scan on int4_tbl a
+ -> Hash
+ -> Seq Scan on int4_tbl b
+ -> Materialize
+ -> Seq Scan on int4_tbl c
+ Filter: (f1 = 0)
+(10 rows)
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+ f1 | f1 | f1
+----+----+----
+ 0 | 0 | 0
+(1 row)
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ QUERY PLAN
+------------------------------------------------
+ Sort
+ Sort Key: b.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: (a.f1 = b.f1)
+ Filter: (COALESCE(a.f1, b.f1) = 0)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+ f1 | f1 | f1
+----+-------------+----
+ 0 | -2147483647 |
+ 0 | -123456 |
+ 0 | 0 | 0
+ 0 | 123456 |
+ 0 | 2147483647 |
+(5 rows)
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: a.f1
+ -> Hash Full Join
+ Hash Cond: (a.f1 = c.f1)
+ Join Filter: ((a.f1 - c.f1) = b.f1)
+ Filter: (COALESCE((a.f1 - c.f1), b.f1) = 123456)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl a
+ -> Materialize
+ -> Seq Scan on int4_tbl b
+ -> Hash
+ -> Seq Scan on int4_tbl c
+(12 rows)
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+ f1 | f1 | f1
+-------------+--------+----
+ -2147483647 | 123456 |
+ -123456 | 123456 |
+ 0 | 123456 |
+ 123456 | 123456 |
+ 2147483647 | 123456 |
+(5 rows)
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
--
-- check handling of join aliases when flattening multiple levels of subquery
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 546d43f3281..74dfd3be6fd 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1771,6 +1771,89 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1,
lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss;
+--
+-- check deductions from FULL JOIN clauses and COALESCE() = constant: the
+-- join clause's operands may appear in either order, but must come from
+-- different inputs of the join
+--
+
+-- operands in reverse order: constants are still deduced for both inputs
+explain (costs off)
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from int4_tbl a full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- ... also when the left input is a join
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- the gating qual must be applied to the join's left input
+explain (costs off)
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+select * from (int4_tbl a join int4_tbl b on a.f1 = b.f1 and a.f1 = (select 1))
+ full join int4_tbl c on c.f1 = a.f1
+ where coalesce(c.f1, a.f1) = 0;
+
+-- an operand may be nulled by a lower outer join, in either order
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on b.f1 = c.f1
+ where coalesce(b.f1, c.f1) = 0;
+
+explain (costs off)
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+select * from (int4_tbl a left join int4_tbl b on a.f1 = b.f1)
+ full join int4_tbl c on c.f1 = b.f1
+ where coalesce(c.f1, b.f1) = 0;
+
+-- nothing can be deduced from a clause relating two vars of the same input
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1, b.f1) = 0
+ order by b.f1;
+
+-- ... nor from a clause with an operand that references both inputs
+explain (costs off)
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 - c.f1 = b.f1 and a.f1 = c.f1
+ where coalesce(a.f1 - c.f1, b.f1) = 123456
+ order by a.f1;
+
+-- if the only join condition relates two vars of the same input, the FULL
+-- JOIN can't be planned, just as without the WHERE condition
+select * from (int4_tbl a cross join int4_tbl b)
+ full join int4_tbl c on a.f1 = b.f1
+ where coalesce(a.f1, b.f1) = 0;
+
--
-- check handling of join aliases when flattening multiple levels of subquery
--
--
2.55.0
