agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19617: Hash node can report incorrect actual rows number
2+ messages / 2 participants
[nested] [flat]
* BUG #19617: Hash node can report incorrect actual rows number
@ 2026-08-12 23:49 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-08-12 23:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: malis@pgrust.com
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 */
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19617: Hash node can report incorrect actual rows number
@ 2026-08-13 17:36 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Ayush Tiwari @ 2026-08-13 17:36 UTC (permalink / raw)
To: malis@pgrust.com; pgsql-bugs@lists.postgresql.org
Hi,
On Thu, 13 Aug 2026 at 21:01, PG Bug reporting form <noreply@postgresql.org>
wrote:
> 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 */
>
Thanks for the report and your analysis.
This appears to be the same issue fixed on July 31 by commit
60826a352d49 and backpatched to PostgreSQL 18 as f70acc8a2b96:
https://postgr.es/c/f70acc8a2
PostgreSQL 18.3 is affected, so upgrading to 18.6 should resolve this.
Regards,
Ayush
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-08-13 17:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-12 23:49 BUG #19617: Hash node can report incorrect actual rows number PG Bug reporting form <noreply@postgresql.org>
2026-08-13 17:36 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox