pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix over-optimistic updating of info about commutable outer join
Date: Sun, 05 Feb 2023 19:25:25 +0000
Message-ID: <E1pOkdf-001kX6-Tp@gemulon.postgresql.org> (raw)

Fix over-optimistic updating of info about commutable outer joins.

make_outerjoininfo was set up to update SpecialJoinInfo's
commute_below, commute_above_l, commute_above_r fields as soon as
it found a pair of outer joins that look like they can commute.
However, this decision could be negated later in the same loop due
to finding an intermediate outer join that prevents commutation.
That left us with commute_xxx fields that were contradictory to the
join order restrictions expressed in min_lefthand/min_righthand.
The latter fields would keep us from actually choosing a bad join
order; but the inconsistent commute_xxx fields could bollix details
such as the varnullingrels values created for intermediate join
relation targetlists, ending in an assertion failure in setrefs.c.

To fix, wait till the end of make_outerjoininfo where we have
accurate values for min_lefthand/min_righthand, and then insert
only relids not present in those sets into the commute_xxx fields.

Per SQLSmith testing by Robins Tharakan.  Note that while Robins
bisected the failure to commit b448f1c8d, it's really the fault of
2489d76c4.  The outerjoin_delayed logic removed in the later commit
was keeping us from deciding that troublesome join pairs commute,
at least in the specific example seen here.

Discussion: https://postgr.es/m/CAEP4nAyAORgE8K_RHSmvWbE9UaChhjbEL1RrDU3neePwwRUB=A@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/b2d0e13a0a4c31167d01e9871f907060c80b8fae

Modified Files
--------------
src/backend/optimizer/plan/initsplan.c | 74 ++++++++++++++++++++++++++--------
src/test/regress/expected/join.out     | 30 ++++++++++++++
src/test/regress/sql/join.sql          | 11 +++++
3 files changed, 99 insertions(+), 16 deletions(-)



Message-ID: <E1pOkdf-001kX6-Tp@gemulon.postgresql.org>
Permalink:  ../E1pOkdf-001kX6-Tp@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1pOkdf-001kX6-Tp@gemulon.postgresql.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: tgl@sss.pgh.pa.us
  Subject: Re: pgsql: Fix over-optimistic updating of info about commutable outer join
  In-Reply-To: <E1pOkdf-001kX6-Tp@gemulon.postgresql.org>
  References: <E1pOkdf-001kX6-Tp@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