Re: BUG #19370: PG18 returns incorrect array slice results when slice bounds depend on another array expression

Hi,

I'm also looking into this.

On 2026-01-06 10:59:41 -0500, Tom Lane wrote:
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > On Tue, 6 Jan 2026 at 21:22, PG Bug reporting form
> > <noreply(at)postgresql(dot)org> wrote:
> >> I have encountered a behavioral regression in PostgreSQL 18 related to array
> >> slicing when the slice start index is computed from another array expression
> >> in the same SELECT list.
>
> > Many thanks for the report and reproducer. I've not looked as to why,
> > but this seems to be caused by a7f107df2. I've included Andres.
>
> After re-reading that patch, I suspect an aliasing problem,
> specifically from this bit in ExecInitSubPlanExpr:
>
> * ... No
> * danger of conflicts with other uses of resvalue/resnull as storing and
> * using the value always is in subsequent steps.
>
> ExecInitExprRec(arg, state,
> &state->resvalue, &state->resnull);
>
> The comment seems perhaps too airy :-(. I experimented with replacing
>
> - &state->resvalue, &state->resnull);
> + resv, resnull);
>
> and indeed that makes the submitted query work --- but it breaks other
> queries in our regression tests, so it's not a usable solution.
>
> I don't see a good reason why ExecInitSubPlanExpr shouldn't be allowed
> to use state->resvalue/resnull this way.

I don't either.

> Given that the problem seems specific to array slicing, I'm suspicious that
> some step in array slicing is failing to cope with input and output datum
> storage being the same, or something close to that.

I think the problem may indeed be an interaction with subscription (I think it
may not require slicing though, I see a similar problem with plain array
indexing).

I think the problem is that ExecBuildProjectionInfo() evaluates, quite
reasonably, each column into state->resvalue (thereby calling
ExecInitExprRec() with resv pointing to
state->resvalue). ExecInitSubscriptingRef() then does:

/*
* Evaluate array input. It's safe to do so into resv/resnull, because we
* won't use that as target for any of the other subexpressions, and it'll
* be overwritten by the final EEOP_SBSREF_FETCH/ASSIGN step, which is
* pushed last.
*/
ExecInitExprRec(sbsref->refexpr, state, resv, resnull);

which puts the input array into state->resvalue. However, I don't think that
can work reliably, because it also evaluates arbitrary expressions afterwards
for the subscripts - if those subscript evaluations also use state->resvalue,
we're in trouble.

I don't quite know yet which step is to blame here.

Greetings,

Andres Freund