Fix some issues with improper placement of outer join clauses.
authorTom Lane <[email protected]>
Wed, 17 May 2023 15:13:52 +0000 (11:13 -0400)
committerTom Lane <[email protected]>
Wed, 17 May 2023 15:14:04 +0000 (11:14 -0400)
commit9df8f903eb6758be5a19e66cdf77e922e9329c31
treeb2726b13ad6c830593a71fd6b1c5ac317a8ef62b
parent867be9c0738bef591544d39985f886b7d8e99bf0
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
14 files changed:
src/backend/optimizer/README
src/backend/optimizer/path/costsize.c
src/backend/optimizer/path/equivclass.c
src/backend/optimizer/path/indxpath.c
src/backend/optimizer/path/joinrels.c
src/backend/optimizer/plan/analyzejoins.c
src/backend/optimizer/plan/initsplan.c
src/backend/optimizer/util/orclauses.c
src/backend/optimizer/util/relnode.c
src/include/nodes/pathnodes.h
src/include/optimizer/pathnode.h
src/include/optimizer/paths.h
src/test/regress/expected/join.out
src/test/regress/sql/join.sql