Fix constant propagation through FULL JOIN clauses Given a FULL JOIN clause "LEFTVAR = RIGHTVAR" and an EquivalenceClass containing the expression 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 however 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 the introduction of 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 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/e50d958f092bc33fcb51089c68c72779bae2a6a3 Modified Files -------------- 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(-)
