MIME-Version: 1.0
References: 
 <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>
In-Reply-To: 
 <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Fri, 3 Jul 2026 07:10:08 +0900
Message-ID: 
 <CAMbWs4_0_DmnVc=oHbkqOCMnwwdM5GUDZzioPA+o4-WTsNnYSQ@mail.gmail.com>
Subject: Re: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG +
 window function
To: Thom Brown <thom@linux.com>
Cc: pgsql-bugs <pgsql-bugs@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAMbWs4_0_DmnVc%3DoHbkqOCMnwwdM5GUDZzioPA%2Bo4-WTsNnYSQ%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 3, 2026 at 1:58=E2=80=AFAM Thom Brown <thom@linux.com> 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