Fix some issues with improper placement of outer join clauses.
After applying outer-join identity 3 in the forward direction,
it was possible for the planner to mistakenly apply a qual clause
from above the two outer joins at the now-lower join level.
This can give the wrong answer, since a value that would get nulled
by the now-upper join might not yet be null.
To fix, when we perform such a transformation, consider that the
now-lower join hasn't really completed the outer join it's nominally
responsible for and thus its relid set should not include that OJ's
relid (nor should its output Vars have that nullingrel bit set).
Instead we add those bits when the now-upper join is performed.
The existing rules for qual placement then suffice to prevent
higher qual clauses from dropping below the now-upper join.
There are a few complications from needing to consider transitive
closures in case multiple pushdowns have happened, but all in all
it's not a very complex patch.
This is all new logic (from 2489d76c4) so no need to back-patch.
The added test cases all have the same results as in v15.
Tom Lane and Richard Guo
Discussion: https://postgr.es/m/0b819232-4b50-f245-1c7d-c8c61bf41827@postgrespro.ru
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/9df8f903eb6758be5a19e66cdf77e922e9329c31
Modified Files
--------------
src/backend/optimizer/README | 21 +++++
src/backend/optimizer/path/costsize.c | 6 +-
src/backend/optimizer/path/equivclass.c | 21 +++--
src/backend/optimizer/path/indxpath.c | 2 +-
src/backend/optimizer/path/joinrels.c | 123 +++++++++++++++++++++++--
src/backend/optimizer/plan/analyzejoins.c | 16 ++--
src/backend/optimizer/plan/initsplan.c | 60 ++++++-------
src/backend/optimizer/util/orclauses.c | 3 +-
src/backend/optimizer/util/relnode.c | 71 +++++++++++----
src/include/nodes/pathnodes.h | 17 ++--
src/include/optimizer/pathnode.h | 1 +
src/include/optimizer/paths.h | 5 +-
src/test/regress/expected/join.out | 143 ++++++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 47 ++++++++++
14 files changed, 450 insertions(+), 86 deletions(-)