public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Guo <[email protected]>
To: Thom Brown <[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 07:10:08 +0900
Message-ID: <CAMbWs4_0_DmnVc=oHbkqOCMnwwdM5GUDZzioPA+o4-WTsNnYSQ@mail.gmail.com> (raw)
In-Reply-To: <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>
References: <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>

On Fri, Jul 3, 2026 at 1:58 AM Thom Brown <[email protected]> wrote:
> CREATE TEMP TABLE t (g int, name text);
> INSERT INTO t VALUES (1, 'a'), (1, 'b'), (2, 'c');
>
> EXPLAIN (VERBOSE)
> SELECT g,
>   JSON_ARRAYAGG(name RETURNING jsonb) AS names,
>   row_number() OVER (ORDER BY g)      AS rn
> FROM t
> GROUP BY g;

> ERROR:  invalid JsonConstructorExpr underlying node type: 6

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.

A simple fix is:

--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12389,6 +12389,8 @@ get_json_agg_constructor(JsonConstructorExpr
*ctor, deparse_context *context,
        get_windowfunc_expr_helper((WindowFunc *) ctor->func, context,
                                   funcname, options.data,
                                   is_json_objectagg);
+   else if (IsA(ctor->func, Var))
+       get_rule_expr((Node *) ctor->func, context, false);
    else
        elog(ERROR, "invalid JsonConstructorExpr underlying node type: %d",
             nodeTag(ctor->func));

Will work on a patch on it.

- Richard

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: <CAMbWs4_0_DmnVc=oHbkqOCMnwwdM5GUDZzioPA+o4-WTsNnYSQ@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