agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: malis@pgrust.com
Subject: BUG #19617: Hash node can report incorrect actual rows number
Date: Wed, 12 Aug 2026 23:49:52 +0000
Message-ID: <19617-10756e8d10b8af53@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19617
Logged by: Michael Malis
Email address: malis@pgrust.com
PostgreSQL version: 18.3
Operating system: MacOS
Description:
ExecBuildHash32Expr compiles a multi-key hash into a chain of
EEOP_HASHDATUM_FIRST[_STRICT] / NEXT32[_STRICT] steps. Only the LAST step
writes the ExprState's resvalue/resnull (what the caller reads); every
earlier
step writes a shared intermediate NullableDatum. The _STRICT variants abort
on
a NULL key by storing NULL into their own output cell and jumping to DONE —
but
for a non-final key that cell is the intermediate, and the jump skips the
final
step, so the ExprState result keeps whatever the previous row's evaluation
left
in it. In hash-join build this makes a NULL non-final-key tuple
counted/bucketed
according to the prior row. Consequence is misleading instrumentation
(order-dependent Hash "actual rows") and a small bucket/memory overcount;
query
RESULTS are unaffected (the rechecked join qual still rejects NULL keys).
Reproducer
----------
The SAME four rows in a DIFFERENT order report different Hash "actual rows"
(3 vs 2); the correct count is 2 in both (two un-matchable NULL-keyed rows).
SET enable_mergejoin = off;
SET enable_nestloop = off;
SET max_parallel_workers_per_gather = 0;
CREATE TEMP TABLE probe (a int, b int);
INSERT INTO probe SELECT 1, 1 FROM generate_series(1, 1000);
ANALYZE probe;
CREATE TEMP TABLE build1 (a int, b int);
INSERT INTO build1 VALUES (1,1), (NULL,1), (2,NULL), (3,3);
ANALYZE build1;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM probe JOIN build1 USING (a, b);
-- -> Hash (actual rows=3.00 ...) (NULL,1) wrongly admitted
CREATE TEMP TABLE build2 (a int, b int);
INSERT INTO build2 VALUES (1,1), (2,NULL), (NULL,1), (3,3);
ANALYZE build2;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM probe JOIN build2 USING (a, b);
-- -> Hash (actual rows=2.00 ...) right, only because the preceding
-- (2,NULL) row left NULL in the cell
Both queries return count = 1000.
The offending code (verbatim, PostgreSQL 18.3)
----------------------------------------------
src/backend/executor/execExprInterp.c — the _STRICT abort stores into the
step's OWN cell (*op->resnull/*op->resvalue = the intermediate, for a
non-final
key) and jumps straight to DONE, past the step that writes the ExprState
result:
1825 EEO_CASE(EEOP_HASHDATUM_FIRST_STRICT)
1826 {
1827 FunctionCallInfo fcinfo = op->d.hashdatum.fcinfo_data;
1828
1829 if (fcinfo->args[0].isnull)
1830 {
...
1836 *op->resnull = true; /* <--
intermediate cell */
1837 *op->resvalue = (Datum) 0;
1838 EEO_JUMP(op->d.hashdatum.jumpdone); /* <--
skips final step */
1839 }
1873 EEO_CASE(EEOP_HASHDATUM_NEXT32_STRICT)
1874 {
1875 FunctionCallInfo fcinfo = op->d.hashdatum.fcinfo_data;
1876
1877 if (fcinfo->args[0].isnull)
1878 {
...
1884 *op->resnull = true; /* <--
intermediate cell */
1885 *op->resvalue = (Datum) 0;
1886 EEO_JUMP(op->d.hashdatum.jumpdone); /* <--
skips final step */
1887 }
src/backend/executor/execExpr.c, ExecBuildHash32Expr — only the final key's
step targets the ExprState result; jumpdone points at the trailing DONE:
4390 if (i == num_exprs - 1)
4391 {
4392 /* the result for hashing the final expr is stored in
the state */
4393 scratch.resvalue = &state->resvalue;
4394 scratch.resnull = &state->resnull;
4395 }
4396 else
4397 {
...
4400 /* intermediate values are stored in an intermediate
result */
4401 scratch.resvalue = &iresult->value;
4402 scratch.resnull = &iresult->isnull;
4403 }
...
4442 as->d.hashdatum.jumpdone = state->steps_len; /* every
strict jumps to DONE */
The caller reads the (stale) ExprState result —
src/backend/executor/nodeHash.c,
MultiExecPrivateHash (function at :138):
173 hashdatum = ExecEvalExprSwitchContext(node->hash_expr,
econtext,
174 &isnull);
175
176 if (!isnull) /* stale
isnull */
...
194 hashtable->totalTuples += 1; /* =
EXPLAIN's actual rows */
view thread (2+ messages) latest in thread
Message-ID: <19617-10756e8d10b8af53@postgresql.org>
Permalink: ../19617-10756e8d10b8af53@postgresql.org/
Also on: postgresql.org/message-id/19617-10756e8d10b8af53@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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, malis@pgrust.com
Subject: Re: BUG #19617: Hash node can report incorrect actual rows number
In-Reply-To: <19617-10756e8d10b8af53@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox