MIME-Version: 1.0
From: Thom Brown <thom@linux.com>
Date: Thu, 2 Jul 2026 17:58:07 +0100
Message-ID: 
 <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>
Subject: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG +
 window function
To: pgsql-bugs <pgsql-bugs@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA-aLv5QYTaMOk%3DQhv6cgwceeHETZV8YJvWZ_rH%2ByVZCuchATA%40mail.gmail.com>
Precedence: bulk

Hi,

EXPLAIN (VERBOSE) throws an internal error when a query's target list
contains an SQL/JSON aggregate (JSON_ARRAYAGG or JSON_OBJECTAGG)
together with a window function (although I suspect it's for anything
that forces a plan node on top of the aggregation node). I ran into it
when trying to construct an obscenely complex SELECT statement.

The query itself executes fine. Only the attempt to deparse its plan
for EXPLAIN (VERBOSE) fails.

I'm using the latest master branch, but I believe the bug is also
present in 16, 17 and 18, since the responsible code has been
unchanged since SQL/JSON constructors were added by commit 7081ac46ace
in 2023.

Debian, Linux 6.12.90 (6.12.90+deb13.1-amd64), x86_64
gcc (Debian 14.2.0-19) 14.2.0
configure options:
  --with-llvm --with-libxml --with-ossp-uuid --with-lz4
  --with-liburing --with-openssl

No non-default server settings are required to reproduce.


Reproducible test case:

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;


Result:

ERROR:  invalid JsonConstructorExpr underlying node type: 6

The same happens with JSON_OBJECTAGG. Note that without EXPLAIN
(VERBOSE), or just using EXPLAIN with any options except VERBOSE, the
query runs and returns correct results.

Removing the window function makes EXPLAIN (VERBOSE) work, because
then a single Aggregate node computes the aggregate and there is no
upper node to reference it from.


Expected result:

 WindowAgg
   Output: g, JSON_ARRAYAGG(name RETURNING jsonb), row_number() OVER w1
   ...
   ->  HashAggregate
         Output: g, jsonb_agg_strict(name)


Regards

Thom