pgsql: Fix mishandling of equivalence-class tests in parameterized plan - Mailing list pgsql-committers

From Tom Lane
Subject pgsql: Fix mishandling of equivalence-class tests in parameterized plan
Date
Msg-id E1awIda-0005LN-S0@gemulon.postgresql.org
Whole thread Raw
List pgsql-committers
Fix mishandling of equivalence-class tests in parameterized plans.

Given a three-or-more-way equivalence class, such as X.Y = Y.Y = Z.Z,
it was possible for the planner to omit one of the quals needed to
enforce that all members of the equivalence class are actually equal.
This only happened in the case of a parameterized join node for two
of the relations, that is a plan tree like

    Nested Loop
      ->  Scan X
      ->  Nested Loop
        ->  Scan Y
        ->  Scan Z
              Filter: Z.Z = X.X

The eclass machinery normally expects to apply X.X = Y.Y when those
two relations are joined, but in this shape of plan tree they aren't
joined until the top node --- and, if the lower nested loop is marked
as parameterized by X, the top node will assume that the relevant eclass
condition(s) got pushed down into the lower node.  On the other hand,
the scan of Z assumes that it's only responsible for constraining Z.Z
to match any one of the other eclass members.  So one or another of
the required quals sometimes fell between the cracks, depending on
whether consideration of the eclass in get_joinrel_parampathinfo()
for the lower nested loop chanced to generate X.X = Y.Y or X.X = Z.Z
as the appropriate constraint there.  If it generated the latter,
it'd erroneously suppose that the Z scan would take care of matters.
To fix, force X.X = Y.Y to be generated and applied at that join node
when this case occurs.

This is *extremely* hard to hit in practice, because various planner
behaviors conspire to mask the problem; starting with the fact that the
planner doesn't really like to generate a parameterized plan of the
above shape.  (It might have been impossible to hit it before we
tweaked things to allow this plan shape for star-schema cases.)  Many
thanks to Alexander Kirkouski for submitting a reproducible test case.

The bug can be demonstrated in all branches back to 9.2 where parameterized
paths were introduced, so back-patch that far.

Branch
------
REL9_3_STABLE

Details
-------
http://git.postgresql.org/pg/commitdiff/67349e5a844728d11e59e54cfedac1d3592e429c

Modified Files
--------------
src/backend/optimizer/path/equivclass.c | 22 ++++++++-
src/backend/optimizer/util/relnode.c    | 82 ++++++++++++++++++++++++++++++---
src/include/optimizer/paths.h           |  5 ++
src/test/regress/expected/join.out      | 32 +++++++++++++
src/test/regress/sql/join.sql           | 17 +++++++
5 files changed, 149 insertions(+), 9 deletions(-)


pgsql-committers by date:

Previous
From: Kevin Grittner
Date:
Subject: pgsql: Add a few entries to the tail of time mapping, to see old values
Next
From: Tom Lane
Date:
Subject: pgsql: Fix mishandling of equivalence-class tests in parameterized plan