Don't replace clone clauses with constant TRUE When reconsider_outer_join_clauses() finds an outer-join clause OUTERVAR = INNERVAR whose OUTERVAR is known to equal a constant, it pushes INNERVAR = CONSTANT into the inner side and replaces the clause with constant TRUE. The replacement assumes that OUTERVAR cannot have gone to NULL where the clause is evaluated.
That assumption fails for a clone made for outer join identity 3. When an outer join is pushed into the RHS of a lower outer join, its relid is not added to the joinrel until the commuted pair is complete, so a version of a clause that lacks that join's nulling bit can be applied above it, after it has null-extended OUTERVAR. This is fine for the original clause, because it is strict, which is what allowed the commutation in the first place; but it is not fine for constant TRUE. As a result, inner rows could be joined to null-extended outer rows, producing wrong query results. To fix, throw back a clone as-is rather than the TRUE replacement, as v15 and earlier did. The derived INNERVAR = CONSTANT clause remains valid and is still pushed down. This can leave a redundant join filter in some plans, as seen in an existing regression test. To keep the thrown-back clause from affecting selectivity estimates, including for parameterized scans, where the cached selectivity is not used, restore the redundancy marker removed by b448f1c8d: a norm_selec greater than 1 marks a redundant clause, for which clause_selectivity_ext() always returns 1.0. Back-patch to v16, where clone clauses were introduced. Bug: #19765 Reported-by: Viktor Leis <[email protected]> Diagnosed-by: Tender Wang <[email protected]> Author: Richard Guo <[email protected]> Co-authored-by: Nitin Motiani <[email protected]> Reviewed-by: Tender Wang <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 16 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/ba7127f240c03f08e888b1b9a228774210fbcf4f Modified Files -------------- src/backend/optimizer/path/clausesel.c | 6 +++ src/backend/optimizer/path/equivclass.c | 24 ++++++++++++ src/include/nodes/pathnodes.h | 5 ++- src/test/regress/expected/join.out | 68 ++++++++++++++++++++++++++++++++- src/test/regress/sql/join.sql | 24 ++++++++++++ 5 files changed, 125 insertions(+), 2 deletions(-)
