Hi, While working on some improvements to join order search, I cobbled together a randomized join generator to shake out bugs in my patches. But it pretty quickly tripped over two long-standing bugs in master. This is the first one, with a proposed fix.
AFAIK the fix is correct (and not very complex). But it's an area of
code I don't know particularly well (or have dealt before). So it really
needs some review. CC Tom, who wrote some of this code.
The gist of the bug is relatively simple - consider this query
SELECT * FROM (t4 AS r2 JOIN t10 AS r3 ON (r2.id = r3.a1))
FULL JOIN t8 AS r4 ON (r4.t4_id = r2.id)
WHERE coalesce(r4.t4_id, r2.id) = 21501;
On a debug build trips this assert in process_implied_equality():
Assert(bms_is_subset(relids, qualscope));
AFAIK the reason is relatively simple - reconsider_full_join_clause is
not careful enough about which input the expressions come from. It seems
to assume that for clause (leftvar = rightvar) the leftvar comes from
left input, and rightvar comes from the right one. Which will be true
for many cases, but does not seem to be guaranteed. I may be missing
something important, of course. Maybe it's an assumption only clauses
with COALESCE break?
Anyway, reconsider_full_join_clause does it's business, creating the
clauses in both inputs, adjusting the EC etc. In assert build this
crashes in process_implied_equality(). In regular builds the query may
produce incorrect results - I think that can happen only with
variable-free clauses, but I'm not sure. (I'm attaching a reproducer for
this.)
But I think there's a second issue, when handling clauses that don't
relate an expression of one input to an expression of the other.
Consider this query (in repro-wrong-result-2.sql):
SELECT *
FROM (t4 r2 CROSS JOIN t10 r3)
FULL JOIN t8 r4 ON r2.id = r3.a1 AND r2.id = r4.t4_id
WHERE coalesce(r2.id, r3.a1) = 1;
AFAIK this pushes both the r2.id=1 and r3.a1=1 to the left side of the
join, which does not seem correct?
The releases before 16 (which invented the JoinDomain stuff) seem to
have only the second issue. And it does not crash on assert.
The commit messages are somewhat longer. It took me a while to
comprehend what's happening (and that it's not due to my patch), so
maybe it's too detailed.
regards
--
Tomas Vondra
From 28c6fe5b81998b2cedc4aff38b7f779066253f41 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Sat, 31 Oct 2026 19:18:37 -0400 Subject: [PATCH v14] Fix constant propagation through FULL JOIN clauses reconsider_full_join_clause() looks for a mergejoinable FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass containing both a constant and COALESCE(LEFTVAR, RIGHTVAR), as produced by a WHERE condition on a merged column of FULL JOIN USING. If it finds both, it derives clauses "LEFTVAR = CONSTANT" and "RIGHTVAR = CONSTANT", which can be pushed down into the join's inputs, and removes the COALESCE member from the EC, so the WHERE condition is not checked anymore. That's valid only if LEFTVAR and RIGHTVAR come from different inputs of the join, but the code never checked that. It holds for the clauses generated for FULL JOIN USING, but not necessarily for a user-written ON clause, and the COALESCE matching doesn't ensure it either. Nothing can be deduced from a clause that doesn't relate an expression of one input to an expression of the other, yet the code derived bogus equalities from such clauses anyway, and dropped the WHERE condition. A COALESCE() above a FULL JOIN can't become part of an EC unless it references all rels of both join inputs (see check_equivalence_delay()), so only a clause with an operand referencing both inputs can hit this. Affected queries may produce incorrect results. Fix by working out which input each operand comes from, and giving up unless the operands come from different inputs. We have to look up the SpecialJoinInfo in join_info_list. The outer_relids of a FULL JOIN clause are all the rels syntactically within the join, which identifies it uniquely.The operands may still appear in either order, which is harmless, as the derived clauses are placed according to their relids. Adjust the comment in distribute_qual_to_rels() that claimed nothing needs full_join_clauses entries to be of the form leftvar = rightvar. Add regression tests with an operand referencing both inputs, and with operands in reversed order, where the deduction is still valid. --- src/backend/optimizer/path/equivclass.c | 37 +++++++++++++++ src/backend/optimizer/plan/initsplan.c | 6 +-- src/test/regress/expected/join.out | 63 +++++++++++++++++++++++++ src/test/regress/sql/join.sql | 27 +++++++++++ 4 files changed, 130 insertions(+), 3 deletions(-) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 2a028d24152..f79b0fa4e86 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2229,6 +2229,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 */ @@ -2249,6 +2250,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 eb498cbe2e8..eb57de51864 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 3d8042ee277..72000b79471 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4046,6 +4046,69 @@ 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) + -- -- 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 9d3bbb94efc..861dcb610d5 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1354,6 +1354,33 @@ 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; + -- -- check handling of join aliases when flattening multiple levels of subquery -- -- 2.55.0
From 8f7ac6e8676ba25e41dcff6d28e28d06a8acd1a4 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Sat, 31 Oct 2026 18:09:54 -0400 Subject: [PATCH vmaster] Fix constant propagation through FULL JOIN clauses reconsider_full_join_clause() looks for a mergejoinable FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass containing both a constant and COALESCE(LEFTVAR, RIGHTVAR). If it finds both, it derives clauses "LEFTVAR = CONSTANT" and "RIGHTVAR = CONSTANT", which can be pushed down into the join's inputs. It then replaces the join clause with constant TRUE and removes the COALESCE member from the EC, so neither the join clause nor the WHERE condition is checked anymore. That's valid only if LEFTVAR and RIGHTVAR come from different inputs of the join, but the code neither checked that nor worked out which input each operand comes from. It assumed that the left operand belongs to the join's lefthand input and the right operand to its righthand input. That's true for the clauses generated for FULL JOIN USING, but not necessarily for a user-written ON clause. The COALESCE matching doesn't ensure it either. This causes two separate problems. The operands may be written in the "wrong" order, in which case each equality gets the other input's JoinDomain. In assert-enabled builds that triggers an assertion in process_implied_equality(). In non-assert builds, the bogus JoinDomain is usually harmless, since derived clauses containing Vars are placed according to their own relids. But a variable-free clause, which is derived if the EC contains another pseudoconstant such as a Param, is applied as a gating qual at the level indicated by the JoinDomain, i.e. on the wrong side of the full join. That may cause the query to produce incorrect results. A second issue is that nothing can be deduced from a clause that doesn't relate an expression of one input to an expression of the other, i.e. if both operands come from the same input, or if an operand references both inputs. The code however derived bogus equalities from such clauses anyway, and discarded the join clause and the WHERE condition. Again, this may produce incorrect query results. Fix by working out which input each operand belongs to, using the SpecialJoinInfo's syn_lefthand and syn_righthand, and giving up unless the operands come from different inputs. Each derived equality is then registered with the JoinDomain of the input its operand comes from. Also adjust the comment in distribute_qual_to_rels() that claimed nothing needs full_join_clauses entries to be of the form leftvar = rightvar. A query whose FULL JOIN condition consists of nothing but a clause of the second kind now fails with "FULL JOIN is only supported with merge-joinable or hash-joinable join conditions", as it already did without the WHERE condition, instead of returning wrong results. Add regression tests with operands in reversed order (with a gating qual), operands from the same input, and an operand referencing both inputs. The JoinDomain problem was introduced in v16. The other problem exists in all supported branches, but before outer-join-aware Vars were introduced in 16 (commit 2489d76c4), it was harder to hit: a COALESCE() above a FULL JOIN couldn't become part of an EC unless it referenced all rels of both join inputs (see check_equivalence_delay()). So only an operand referencing both inputs can trigger it there. --- src/backend/optimizer/path/equivclass.c | 36 ++++++-- src/backend/optimizer/plan/initsplan.c | 7 +- src/test/regress/expected/join.out | 105 ++++++++++++++++++++++++ src/test/regress/sql/join.sql | 40 +++++++++ 4 files changed, 177 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 4ec9291833b..d6226701739 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6556,6 +6556,111 @@ 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 +-- +-- 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) + +-- 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) + -- -- 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 381c57a5a88..14f7c69df3d 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2278,6 +2278,46 @@ 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 +-- + +-- 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; + +-- 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; + -- -- check handling of join aliases when flattening multiple levels of subquery -- -- 2.55.0
repro.sql
Description: application/sql
repro-14.sql
Description: application/sql
repro-wrong-result-1.sql
Description: application/sql
repro-wrong-result-2.sql
Description: application/sql
