Re: bug: query returns different result with and without memoization.

On Sat, Jul 25, 2026 at 5:04 AM Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)com> wrote:
> --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;

Thanks for the report! ExecReScanMemoize() purges the whole cache
only when a parameter that is not part of the cache key changes. It
assumes that a change to a cache-key parameter is handled by looking
up a different cache entry. But that assumption fails when a
parameter is buried inside a larger cache-key expression and also
appears elsewhere in the subplan.

In the reported case, there is a cache key of "t2.hundred + t0.ten"
and a separate filter "t1.twenty = t0.ten". The problem is that
different (t2.hundred, t0.ten) pairs can produce the same cache-key
value while the filter selects different rows.

I think we can fix it by dropping from keyparamids any parameter that
also appears outside the cache-key expressions. See the attached
patch.

- Richard