From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix hash join when inner hashkey expressions contain Params.
Date: Tue, 20 Jun 2023 21:48:09 +0000
Message-ID: <E1qBjCq-002nDu-Pj@gemulon.postgresql.org> (raw)
Fix hash join when inner hashkey expressions contain Params.
If the inner-side expressions contain PARAM_EXEC Params, we must
re-hash whenever the values of those Params change. The executor
mechanism for that exists already, but we failed to invoke it because
finalize_plan() neglected to search the Hash.hashkeys field for
Params. This allowed a previous scan's hash table to be re-used
when it should not be, leading to rows missing from the join's output.
(I believe incorrectly-included join rows are impossible however,
since checking the real hashclauses would reject false matches.)
This bug is very ancient, dating probably to d24d75ff1 of 7.4.
Sadly, this simple fix depends on the plan representational changes
made by 2abd7ae9b, so it will only work back to v12. I thought
about trying to make some kind of hack for v11, but I'm leery
of putting code significantly different from what is used in the
newer branches into a nearly-EOL branch. Seeing that the bug
escaped detection for a full twenty years, problematic cases
must be rare; so I don't feel too awful about leaving v11 as-is.
Per bug #17985 from Zuming Jiang. Back-patch to v12.
Discussion: https://postgr.es/m/17985-748b66607acd432e@postgresql.org
Branch
------
REL_14_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/d911dce14d766a16c4e5b98b54960ef4df857c3b
Modified Files
--------------
src/backend/optimizer/plan/subselect.c | 6 +++++-
src/test/regress/expected/join_hash.out | 36 +++++++++++++++++++++++++++++++++
src/test/regress/sql/join_hash.sql | 19 +++++++++++++++++
3 files changed, 60 insertions(+), 1 deletion(-)
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 hash join when inner hashkey expressions contain Params.
In-Reply-To: <E1qBjCq-002nDu-Pj@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