public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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 12:06:45 +0900
Message-ID: <CAMbWs4-b2By5XFEn-_ZJKgN4-8SBhAoRZJrt4gwmM2ctOQzAyw@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs4_0_DmnVc=oHbkqOCMnwwdM5GUDZzioPA+o4-WTsNnYSQ@mail.gmail.com>
References: <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>
<CAMbWs4_0_DmnVc=oHbkqOCMnwwdM5GUDZzioPA+o4-WTsNnYSQ@mail.gmail.com>
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.
- Richard
Attachments:
[application/octet-stream] v1-0001-Fix-EXPLAIN-failure-when-deparsing-SQL-JSON-aggre.patch (7.9K, ../CAMbWs4-b2By5XFEn-_ZJKgN4-8SBhAoRZJrt4gwmM2ctOQzAyw@mail.gmail.com/2-v1-0001-Fix-EXPLAIN-failure-when-deparsing-SQL-JSON-aggre.patch)
download | inline diff:
From b917e5c2a20dd469da6a9deb248cb5dc42bca511 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Fri, 3 Jul 2026 10:52:29 +0900
Subject: [PATCH v1] Fix EXPLAIN failure when deparsing SQL/JSON aggregates
If an expression containing an aggregate is evaluated above the plan
node that computes the aggregate, as happens with window functions or
with expressions postponed to above the final sort, setrefs.c replaces
the Aggref or WindowFunc with a Var referencing the lower node's
output. For SQL/JSON aggregates such as JSON_ARRAYAGG, deparsing the
containing JsonConstructorExpr then failed with "invalid
JsonConstructorExpr underlying node type", since
get_json_agg_constructor() did not expect a Var there.
Fix by printing the Var, matching how other expressions evaluated
above an aggregate are displayed. It's a bit annoying that the
original JSON aggregate syntax then appears nowhere in the EXPLAIN
output, but there is no good way to print it: the JsonConstructorExpr
wrapper and the Aggref it decorates end up in different plan nodes,
and neither alone suffices to reconstruct the syntax.
---
src/backend/utils/adt/ruleutils.c | 9 +++
src/test/regress/expected/sqljson.out | 95 +++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 35 ++++++++++
3 files changed, 139 insertions(+)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 88de5c0481c..05b769dce78 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12389,6 +12389,15 @@ 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))
+ {
+ /*
+ * If the aggregate is computed by a lower plan node, setrefs.c will
+ * have replaced the Aggref or WindowFunc with a Var referencing that
+ * node's output. Just print the Var.
+ */
+ (void) get_variable((Var *) ctor->func, 0, false, context);
+ }
else
elog(ERROR, "invalid JsonConstructorExpr underlying node type: %d",
nodeTag(ctor->func));
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 0f337bda325..3e5a0508ab0 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1757,3 +1757,98 @@ SELECT JSON_OBJECT('a': JSON_OBJECTAGG('b': 1 RETURNING text) FORMAT JSON);
(1 row)
DROP FUNCTION volatile_one, stable_one;
+-- Test deparsing of a JSON aggregate that is computed below a WindowAgg
+-- node.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT i % 2 AS g,
+ JSON_ARRAYAGG(i RETURNING jsonb) AS ja,
+ JSON_OBJECTAGG(i: i RETURNING jsonb) AS jo,
+ row_number() OVER (ORDER BY i % 2) AS rn
+FROM generate_series(1, 3) i
+GROUP BY i % 2;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ WindowAgg
+ Output: ((i % 2)), (jsonb_agg_strict(i)), (jsonb_object_agg(i, i)), row_number() OVER w1
+ Window: w1 AS (ORDER BY ((i.i % 2)) ROWS UNBOUNDED PRECEDING)
+ -> GroupAggregate
+ Output: ((i % 2)), jsonb_agg_strict(i), jsonb_object_agg(i, i)
+ Group Key: ((i.i % 2))
+ -> Sort
+ Output: ((i % 2)), i
+ Sort Key: ((i.i % 2))
+ -> Function Scan on pg_catalog.generate_series i
+ Output: (i % 2), i
+ Function Call: generate_series(1, 3)
+(12 rows)
+
+SELECT i % 2 AS g,
+ JSON_ARRAYAGG(i RETURNING jsonb) AS ja,
+ JSON_OBJECTAGG(i: i RETURNING jsonb) AS jo,
+ row_number() OVER (ORDER BY i % 2) AS rn
+FROM generate_series(1, 3) i
+GROUP BY i % 2;
+ g | ja | jo | rn
+---+--------+------------------+----
+ 0 | [2] | {"2": 2} | 1
+ 1 | [1, 3] | {"1": 1, "3": 3} | 2
+(2 rows)
+
+-- The same, but with the JSON aggregate used as a window function that is
+-- computed below another WindowAgg node.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT JSON_ARRAYAGG(i RETURNING jsonb) OVER (ORDER BY i DESC) AS ja,
+ row_number() OVER (ORDER BY i) AS rn
+FROM generate_series(1, 3) i;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ WindowAgg
+ Output: (jsonb_agg_strict(i) OVER w1), row_number() OVER w2, i
+ Window: w2 AS (ORDER BY i.i ROWS UNBOUNDED PRECEDING)
+ -> Sort
+ Output: i, (jsonb_agg_strict(i) OVER w1)
+ Sort Key: i.i
+ -> WindowAgg
+ Output: i, jsonb_agg_strict(i) OVER w1
+ Window: w1 AS (ORDER BY i.i)
+ -> Sort
+ Output: i
+ Sort Key: i.i DESC
+ -> Function Scan on pg_catalog.generate_series i
+ Output: i
+ Function Call: generate_series(1, 3)
+(15 rows)
+
+SELECT JSON_ARRAYAGG(i RETURNING jsonb) OVER (ORDER BY i DESC) AS ja,
+ row_number() OVER (ORDER BY i) AS rn
+FROM generate_series(1, 3) i;
+ ja | rn
+-----------+----
+ [3, 2, 1] | 1
+ [3, 2] | 2
+ [3] | 3
+(3 rows)
+
+-- The same, but with the expression containing the JSON aggregate postponed
+-- to above the final sort due to being volatile.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT i % 2 AS g,
+ JSON_ARRAYAGG(i RETURNING jsonb) || to_jsonb(random()) AS ja
+FROM generate_series(1, 3) i
+GROUP BY i % 2
+ORDER BY count(*);
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Result
+ Output: ((i % 2)), ((jsonb_agg_strict(i)) || to_jsonb(random())), (count(*))
+ -> Sort
+ Output: ((i % 2)), (count(*)), (jsonb_agg_strict(i))
+ Sort Key: (count(*))
+ -> HashAggregate
+ Output: ((i % 2)), count(*), jsonb_agg_strict(i)
+ Group Key: (i.i % 2)
+ -> Function Scan on pg_catalog.generate_series i
+ Output: (i % 2), i
+ Function Call: generate_series(1, 3)
+(11 rows)
+
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index a68747733a1..3a666eca128 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -706,3 +706,38 @@ SELECT JSON_OBJECT('a': JSON_OBJECTAGG('b': stable_one() RETURNING text) FORMAT
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_OBJECT('a': JSON_OBJECTAGG('b': 1 RETURNING text) FORMAT JSON);
SELECT JSON_OBJECT('a': JSON_OBJECTAGG('b': 1 RETURNING text) FORMAT JSON);
DROP FUNCTION volatile_one, stable_one;
+
+-- Test deparsing of a JSON aggregate that is computed below a WindowAgg
+-- node.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT i % 2 AS g,
+ JSON_ARRAYAGG(i RETURNING jsonb) AS ja,
+ JSON_OBJECTAGG(i: i RETURNING jsonb) AS jo,
+ row_number() OVER (ORDER BY i % 2) AS rn
+FROM generate_series(1, 3) i
+GROUP BY i % 2;
+SELECT i % 2 AS g,
+ JSON_ARRAYAGG(i RETURNING jsonb) AS ja,
+ JSON_OBJECTAGG(i: i RETURNING jsonb) AS jo,
+ row_number() OVER (ORDER BY i % 2) AS rn
+FROM generate_series(1, 3) i
+GROUP BY i % 2;
+
+-- The same, but with the JSON aggregate used as a window function that is
+-- computed below another WindowAgg node.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT JSON_ARRAYAGG(i RETURNING jsonb) OVER (ORDER BY i DESC) AS ja,
+ row_number() OVER (ORDER BY i) AS rn
+FROM generate_series(1, 3) i;
+SELECT JSON_ARRAYAGG(i RETURNING jsonb) OVER (ORDER BY i DESC) AS ja,
+ row_number() OVER (ORDER BY i) AS rn
+FROM generate_series(1, 3) i;
+
+-- The same, but with the expression containing the JSON aggregate postponed
+-- to above the final sort due to being volatile.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT i % 2 AS g,
+ JSON_ARRAYAGG(i RETURNING jsonb) || to_jsonb(random()) AS ja
+FROM generate_series(1, 3) i
+GROUP BY i % 2
+ORDER BY count(*);
--
2.39.5 (Apple Git-154)
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-b2By5XFEn-_ZJKgN4-8SBhAoRZJrt4gwmM2ctOQzAyw@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