public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thom Brown <[email protected]>
To: Richard Guo <[email protected]>
Cc: pgsql-bugs <[email protected]>
Subject: Re: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG + window function
Date: Tue, 7 Jul 2026 15:20:14 +0100
Message-ID: <CAA-aLv6F_ebaU6Czuht5NTRQog0r-TpR9emV-qDKUnGe=eR8Lw@mail.gmail.com> (raw)
In-Reply-To: <CAA-aLv5Tybb_PAG8vmxB8KJXNgwqEBtAnd_nneP2YNahxs9irg@mail.gmail.com>
References: <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>
	<CAMbWs4_0_DmnVc=oHbkqOCMnwwdM5GUDZzioPA+o4-WTsNnYSQ@mail.gmail.com>
	<CAMbWs4-b2By5XFEn-_ZJKgN4-8SBhAoRZJrt4gwmM2ctOQzAyw@mail.gmail.com>
	<CAA-aLv6p3tMdY4KJ9wG_DxL1_3MTMCeKj8zUo+1-MctSBmvfdw@mail.gmail.com>
	<CAMbWs4_+Xh6JRTUrxBHhr2-5e+5Y74vne_xR_Dr_=Y0t=5cjgg@mail.gmail.com>
	<CAA-aLv5Tybb_PAG8vmxB8KJXNgwqEBtAnd_nneP2YNahxs9irg@mail.gmail.com>

On Tue, 7 Jul 2026 at 14:49, Thom Brown <[email protected]> wrote:
>
> On Mon, 6 Jul 2026 at 03:17, Richard Guo <[email protected]> wrote:
> >
> > On Fri, Jul 3, 2026 at 5:36 PM Thom Brown <[email protected]> wrote:
> > > Thanks for taking a look at this. It's unfortunate that reconstructing
> > > the syntax is problematic, because in the case of the original query
> > > that tripped on this bug, I lose the following from my original query:
> > > ...
> > > I guess the relevant information can be gleaned from this, but not for
> > > JSON_ARRAYAGG(i RETURNING text) because we get the same output whether
> > > we're returning text or json.
> >
> > Yeah, the v1 patch is lossy, and the lost information cannot be
> > recovered from the plan at all.
> >
> > So I'm switching back to the resolve_special_varno() reconstruction I
> > had tried and set aside.  My earlier objection was that it makes the
> > WindowAgg look like it computes a JSON aggregate when it only passes
> > through a value computed below.  But the lower node still prints
> > jsonb_agg_strict(name), so the plan does show where the aggregate is
> > computed, and I think that is a smaller cost than losing the
> > information outright.  Also, partial aggregation already deparses this
> > way.  The combining aggregate's argument is a Var referencing the
> > partial aggregate's output, and get_agg_expr() resolves it back with
> > resolve_special_varno() to reprint the aggregate at the finalizing
> > node.
> >
> > I still don't want to hack the planner to keep the JsonConstructorExpr
> > with its Aggref in make_window_input_target(), because I still think
> > that that is too invasive and changes which node evaluates the
> > wrapper.
> >
> > Hence, I end up with the attached v2 patch.
>
> I've tested it against the output from the 1st patch.
>
> Patch 1:
> ((jsonb_agg_strict(e.emp_name ORDER BY e.emp_name)))
>
> Patch 2:
> (JSON_ARRAYAGG(e.emp_name ORDER BY e.emp_name RETURNING jsonb))
>
> 1:
> ((jsonb_object_agg_strict(e.emp_name, e.salary)))
>
> 2:
> (JSON_OBJECTAGG(e.emp_name : e.salary ABSENT ON NULL RETURNING jsonb))
>
> With that last one, why is that colon there?

Actually, I don't know what I was thinking. This is clearly a
key:value pairing, so ignore that remark. But we do get more info with
this line in the 2nd patch.

So all looks good to me.

Thanks

Thom

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG + window function
  In-Reply-To: <CAA-aLv6F_ebaU6Czuht5NTRQog0r-TpR9emV-qDKUnGe=eR8Lw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox