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

On Mon, Nov 2, 2026 at 11:33 AM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> Codex reported me that, after this change, the same query can fail with
> enable_memoize enabled but succeed with it disabled, as shown below.
> Is this a bug?

Thanks for the report! Yes, both are bugs, and they have the same
cause. When looking for Params to make cache keys of, we collect
every PARAM_EXEC Param we find, but only those supplied by outer query
levels are safe to evaluate as cache keys.

In your first query, the output Param of the ALL SubPlan is made a
cache key, but it has no value outside the SubPlan's testexpr, hence
the "plan should not reference subplan's variable" error. In your
second query, the initPlan's output Param is made a cache key, so
Memoize runs the initPlan when it evaluates the cache keys, even
though the CASE arm that needs it is never taken.

Attached is a patch that fixes it by making cache keys only of the
Params listed in some outer query level's plan_params. I plan to
back-patch it to v14.

- Richard