bug: query returns different result with and without memoization.

From: Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: bug: query returns different result with and without memoization.
Date: 2026-07-24 20:04:34
Message-ID: CA+COZaAHZT7T2G2F0-drdQB+StWJxOzqeDxp=ESp+YUiy-kOaQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

The same query can return different results with/without memoization
enabled.
Here is a reproduction on current master.

--setup
CREATE TABLE tenk1 AS
SELECT g AS unique1, g%2 AS two, g%10 AS ten, g%20 AS twenty, g%100 AS
hundred
FROM generate_series(0,9999) g;
CREATE INDEX tenk1_unique1 ON tenk1(unique1);
CREATE INDEX tenk1_hundred ON tenk1(hundred);
ANALYZE tenk1;

SET enable_seqscan=off; SET enable_mergejoin=off; SET work_mem='64kB';

Now return the query below with each of these settings:

SET enable_memoize = on; -- 82000 (incorrect)
SET enable_memoize = off; -- 100000 (correct)

SELECT sum(c) FROM (
SELECT t0.unique1,
(SELECT count(*) FROM tenk1 t2 JOIN tenk1 t1
ON t1.unique1 = t2.hundred + t0.ten
WHERE t1.twenty = t0.ten) AS c
FROM tenk1 t0 WHERE t0.unique1 < 200) s;

This is an extension of the problem addressed here
https://www.postgresql.org/message-id/17213-988ed34b225a2862@postgresql.org
that was not resolved by the original patch.

Browse pgsql-hackers by date

  From Date Subject
Next Message Andreas Karlsson 2026-07-24 20:39:54 Re: [DESIGN] Soft DROP TABLE, recoverable drops for PostgreSQL
Previous Message Nathan Bossart 2026-07-24 20:01:13 Re: glibc qsort() vulnerability