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: Fri, 3 Jul 2026 09:36:09 +0100
Message-ID: <CAA-aLv6p3tMdY4KJ9wG_DxL1_3MTMCeKj8zUo+1-MctSBmvfdw@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs4-b2By5XFEn-_ZJKgN4-8SBhAoRZJrt4gwmM2ctOQzAyw@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>

On Fri, 3 Jul 2026 at 04:06, Richard Guo <[email protected]> wrote:
>
> On Fri, Jul 3, 2026 at 7:10 AM Richard Guo <[email protected]> wrote:
> > Reproduced here.  get_json_agg_constructor() expects that ctor->func
> > is Aggref or WindowFunc, but what it gets here is a Var.
> >
> > This is because the query has both window functions and grouped
> > aggregates.  make_window_input_target() flattens the final tlist using
> > pull_var_clause, which pulls the Aggref out of its JsonConstructorExpr
> > wrapper.  Then fix_upper_expr() matches that inner Aggref against the
> > Agg subplan's tlist and replaces it with an OUTER Var.
>
> Here is the patch.  It's a bit annoying that the original JSON agg
> syntax then appears nowhere in the EXPLAIN output.  All we get is the
> bare jsonb_agg_strict Aggref.
>
>  WindowAgg
>    Output: g, (jsonb_agg_strict(name)), row_number() OVER w1
>    Window: w1 AS (ROWS UNBOUNDED PRECEDING)
>    ->  HashAggregate
>          Output: g, jsonb_agg_strict(name)
>
> The reason is that the JsonConstructorExpr wrapper and the Aggref it
> wraps end up in different plan nodes, and neither alone suffices to
> reconstruct the syntax.
>
> I had an attempt to reconstruct the JSON syntax for the WindowAgg node
> by leveraging resolve_special_varno(), and that works.  But I don't
> know how to do that for the HashAggregate node, because the
> JsonConstructorExpr wrapper simply doesn't exist at that plan level.
> Maybe we can hack the planner to make make_window_input_target() keep
> the JsonConstructorExpr together with its Aggref.  But I think that is
> too invasive and it changes which node evaluates the wrapper.
>
> So that attempt ended up with:
>
>  WindowAgg
>    Output: g, JSON_ARRAYAGG(name RETURNING jsonb), row_number() OVER w1
>    Window: w1 AS (ROWS UNBOUNDED PRECEDING)
>    ->  HashAggregate
>          Output: g, jsonb_agg_strict(name)
>
> But I don't think this is good.  It fails to state the fact that the
> WindowAgg doesn't compute a JSON aggregate; it passes through a value
> that the HashAggregate computed.  So I gave up this idea.

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:

JSON_ARRAYAGG(i RETURNING jsonb)
is instead:
jsonb_agg_strict(i)

And the same with: JSON_ARRAYAGG(…)

JSON_ARRAYAGG(i RETURNING json)
is instead:
json_agg_strict(i)

And the same with: JSON_ARRAYAGG(i RETURNING text)

WITH UNIQUE KEYS
is instead:
json_object_agg_unique(i,i)

NULL ON NULL
This isn't reconstructed

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.

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-aLv6p3tMdY4KJ9wG_DxL1_3MTMCeKj8zUo+1-MctSBmvfdw@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