public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thom Brown <[email protected]>
To: pgsql-bugs <[email protected]>
Subject: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG + window function
Date: Thu, 2 Jul 2026 17:58:07 +0100
Message-ID: <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com> (raw)

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

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]
  Subject: Re: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG + window function
  In-Reply-To: <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@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