From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix some issues with improper placement of outer join clauses.
Date: Wed, 17 May 2023 15:14:16 +0000
Message-ID: <E1pzIr2-000Lux-Tc@gemulon.postgresql.org> (raw)
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(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix some issues with improper placement of outer join clauses.
In-Reply-To: <E1pzIr2-000Lux-Tc@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox