pgsql: Fix wrong results from Memoize with a Param inside a cache key

Fix wrong results from Memoize with a Param inside a cache key

ExecReScanMemoize() purges the whole cache only when a Param changes
which is not part of the cache key; a change to a cache-key Param is
assumed to be handled by looking up a different cache entry. To that
end, create_memoize_plan() put every Param appearing in the cache key
expressions into keyparamids. That assumption holds only when the
cache key values determine the Param's value.

It fails for an upper-level Param which is buried inside a larger
cache-key expression and also appears elsewhere in the subplan. With
a cache key of "t2.hundred + t0.ten" and a filter "t1.twenty =
t0.ten", different (t2.hundred, t0.ten) pairs produce the same
cache-key value while the filter selects different rows, so one cache
entry wrongly serves both. Since t0.ten was included in keyparamids,
the cache was never purged when it changed.

Also, pull_paramids() collected PARAM_EXTERN Params too, whose numbers
are unrelated to those of PARAM_EXEC Params. An external Param in a
cache key could therefore keep a change to an unrelated executor Param
from purging the cache.

To fix, put into keyparamids only the Params whose values the cache
keys determine. These are the nestloop Params which replace the outer
rel's Vars in the cache keys, as the inner scan can only reference the
outer rel through the cache keys, and, in binary mode only, a Param
which is a cache key by itself. In logical mode the hash equality
operator may not distinguish values that affect the results
differently. Any other Param now purges the cache when it changes.
Also make pull_paramids() collect PARAM_EXEC Params only.

Purging alone would empty the cache on every rescan of the enclosing
plan whenever a buried Param changes, even when the Param is used
nowhere else and the old code was correct and kept its cache entries
across rescans. So also make each buried Param a cache key by itself,
which puts Memoize into binary mode, so that cache entries survive
across rescans with the same Param value. A buried Param whose type
can't be hashed is left to the purging, rather than giving up on
Memoize.

Back-patch to all supported branches.

Reported-by: Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)com>
Author: Richard Guo <guofenglinux(at)gmail(dot)com>
Reviewed-by: David Rowley <dgrowleyml(at)gmail(dot)com>
Reviewed-by: Yilin Zhang <jiezhilove(at)126(dot)com>
Discussion: https://postgr.es/m/CA+COZaAHZT7T2G2F0-drdQB+StWJxOzqeDxp=ESp+YUiy-kOaQ@mail.gmail.com
Backpatch-through: 14

Branch
------
REL_18_STABLE

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

Modified Files
--------------
src/backend/optimizer/path/joinpath.c | 112 ++++++++++++++++++++++--------
src/backend/optimizer/plan/createplan.c | 27 +++++++-
src/backend/optimizer/util/clauses.c | 6 +-
src/include/nodes/execnodes.h | 3 +-
src/include/nodes/plannodes.h | 2 +-
src/test/regress/expected/memoize.out | 116 ++++++++++++++++++++++++++++++++
src/test/regress/sql/memoize.sql | 59 ++++++++++++++++
7 files changed, 292 insertions(+), 33 deletions(-)