public inbox for [email protected]
help / color / mirror / Atom feedFrom: Richard Guo <[email protected]>
To: Tom Lane <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Álvaro Herrera <[email protected]>
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
Date: Tue, 3 Mar 2026 23:32:12 +0900
Message-ID: <CAMbWs4-NuYP+0Zq9=Ufwy5GcM0XQnEqWjJFucaRGO4EQgiCcow@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs49P2z40f5yb_97ErwtC9ocdR5QQN0gWUZtjgot9+iGAcg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAMbWs4_4Zc7O4pCU_nJU_8=Y2bOS3sEXJR=WH39Kc__UuaCW3w@mail.gmail.com>
<CAMbWs4_f-4BqvfKTt0YbkOky+Gf0Mpi2kCT3CAE8brZFnK275Q@mail.gmail.com>
<CAMbWs4-y4CEy66yA-Hh+6Snqbok3XTMFj5h87gUk_15Hhjd3qw@mail.gmail.com>
<[email protected]>
<CAMbWs49P2z40f5yb_97ErwtC9ocdR5QQN0gWUZtjgot9+iGAcg@mail.gmail.com>
On Tue, Mar 3, 2026 at 10:03 AM Richard Guo <[email protected]> wrote:
> That is a good point I hadn't considered. So I think the ideal fix is
> to have the parser preserve the user's original JSON_ARRAY(query)
> syntax as much as possible, and then defer the JSON_ARRAYAGG rewrite
> trick to the planner, perhaps during expression preprocessing.
I tried hacking on this idea to see how it would look in practice, and
here is what I got.
- Richard
Attachments:
[application/octet-stream] v2-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch (21.7K, 2-v2-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch)
download | inline diff:
From 99fe0f82702e89f692133561e59c6829ffd0558b Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Mon, 2 Mar 2026 12:11:00 +0900
Subject: [PATCH v2] Fix JSON_ARRAY(query) empty set handling and view
deparsing
According to the SQL/JSON standard, JSON_ARRAY(query) must return an
empty JSON array ('[]') when the subquery returns zero rows.
Previously, the parser rewrote JSON_ARRAY(query) into a JSON_ARRAYAGG
aggregate function. Because this aggregate evaluates to NULL over an
empty set without a GROUP BY clause, the constructor erroneously
returned NULL. Additionally, this premature rewrite baked physical
implementation details into the catalog, preventing ruleutils.c from
deparsing the original syntax for views.
This patch resolves both issues by preserving the user's semantic
intent in the parsetree. A new JSCTOR_JSON_ARRAY_QUERY node type is
introduced to identify query constructors. The parser now emits this
node wrapping a standard EXPR_SUBLINK, allowing ruleutils.c to
recognize and deparse the original syntax.
The physical transformation is now deferred to the planner. During
planning, the user's subquery is wrapped in an outer SELECT block, so
that clauses like ORDER BY and LIMIT are evaluated before the
JSON_ARRAYAGG aggregate is applied. A fully analyzed Aggref is then
attached to this wrapper, and the entire construct is encapsulated in
a COALESCE expression. This ensures that a NULL resulting from an
empty set is caught and replaced with an empty JSON array.
---
doc/src/sgml/func/func-json.sgml | 3 +-
src/backend/optimizer/util/clauses.c | 176 +++++++++++++++++++++++++-
src/backend/parser/parse_expr.c | 76 +++--------
src/backend/utils/adt/ruleutils.c | 20 +++
src/include/nodes/primnodes.h | 15 ++-
src/test/regress/expected/sqljson.out | 92 +++++++++++++-
src/test/regress/sql/sqljson.sql | 38 ++++++
7 files changed, 351 insertions(+), 69 deletions(-)
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 1ec73cff464..d510ba8375b 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -620,7 +620,8 @@
which must be a SELECT query returning a single column. If
<literal>ABSENT ON NULL</literal> is specified, NULL values are ignored.
This is always the case if a
- <replaceable>query_expression</replaceable> is used.
+ <replaceable>query_expression</replaceable> is used. If the query returns
+ no rows, an empty JSON array is returned.
</para>
<para>
<literal>json_array(1,true,json '{"a":null}')</literal>
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a41d81734cf..43827af2fe0 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -20,6 +20,7 @@
#include "postgres.h"
#include "access/htup_details.h"
+#include "catalog/pg_aggregate.h"
#include "catalog/pg_class.h"
#include "catalog/pg_language.h"
#include "catalog/pg_operator.h"
@@ -133,6 +134,7 @@ static Expr *simplify_function(Oid funcid,
eval_const_expressions_context *context);
static Node *simplify_aggref(Aggref *aggref,
eval_const_expressions_context *context);
+static Node *rewrite_json_array_subquery(JsonConstructorExpr *expr);
static List *reorder_function_arguments(List *args, int pronargs,
HeapTuple func_tuple);
static List *add_function_defaults(List *args, int pronargs,
@@ -3010,7 +3012,6 @@ eval_const_expressions_mutator(Node *node,
}
break;
}
-
case T_JsonValueExpr:
{
JsonValueExpr *jve = (JsonValueExpr *) node;
@@ -3034,7 +3035,18 @@ eval_const_expressions_mutator(Node *node,
(Expr *) formatted_expr,
copyObject(jve->format));
}
+ case T_JsonConstructorExpr:
+ {
+ JsonConstructorExpr *jce = (JsonConstructorExpr *) node;
+ if (jce->type == JSCTOR_JSON_ARRAY_QUERY)
+ {
+ Node *rewritten = rewrite_json_array_subquery(jce);
+
+ return eval_const_expressions_mutator(rewritten, context);
+ }
+ }
+ break;
case T_SubPlan:
case T_AlternativeSubPlan:
@@ -4378,6 +4390,168 @@ simplify_aggref(Aggref *aggref, eval_const_expressions_context *context)
return (Node *) aggref;
}
+/*
+ * Subroutine for eval_const_expressions: transform a JSCTOR_JSON_ARRAY_QUERY
+ * expression into its executable form:
+ *
+ * COALESCE((SELECT JSON_ARRAYAGG(col) FROM (user_query)), '[]')
+ *
+ * This is done during planning rather than parse analysis so that ruleutils.c
+ * can deparse the original syntax for views.
+ *
+ * The user's query is wrapped in an outer SELECT so that clauses like ORDER BY
+ * and LIMIT are evaluated before aggregation. COALESCE ensures that we return
+ * an empty array rather than NULL on an empty result set, per the SQL/JSON
+ * standard.
+ */
+static Node *
+rewrite_json_array_subquery(JsonConstructorExpr *expr)
+{
+ SubLink *sublink = linitial_node(SubLink, expr->args);
+ SubLink *newsublink;
+ Query *subquery;
+ Query *newquery;
+ List *colnames;
+ TargetEntry *tle;
+ RangeTblEntry *rte;
+ RangeTblRef *rtr;
+ Var *newvar;
+ JsonConstructorExpr *newjsctor;
+ Aggref *aggref;
+ Const *empty_json_const;
+ Oid aggfnoid;
+ Oid aggtype;
+ Oid typinput;
+ Oid typioparam;
+ CoalesceExpr *coalesce;
+
+ Assert(expr->type == JSCTOR_JSON_ARRAY_QUERY);
+ Assert(sublink->subLinkType == EXPR_SUBLINK);
+
+ newsublink = copyObject(sublink);
+ subquery = (Query *) newsublink->subselect;
+
+ /*
+ * Since we are pushing the subquery down one level, we must increment the
+ * sublevels of any outer references it contains.
+ */
+ IncrementVarSublevelsUp((Node *) subquery, 1, 1);
+
+ /* Most of the upper Query struct can be left as zeroes/nulls */
+ newquery = makeNode(Query);
+ newquery->commandType = CMD_SELECT;
+ newquery->querySource = subquery->querySource;
+ newquery->canSetTag = true;
+
+ tle = linitial_node(TargetEntry, subquery->targetList);
+ colnames = list_make1(makeString(tle->resname ?
+ pstrdup(tle->resname) :
+ pstrdup("json_col")));
+
+ /* Build a suitable RTE for the subquery */
+ rte = makeNode(RangeTblEntry);
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = subquery;
+ rte->eref = makeAlias("json_array_subquery", colnames);
+ rte->lateral = false;
+ rte->inh = false;
+ rte->inFromCl = true;
+ newquery->rtable = list_make1(rte);
+
+ /* Build a suitable jointree for the subquery */
+ rtr = makeNode(RangeTblRef);
+ rtr->rtindex = 1;
+ newquery->jointree = makeFromExpr(list_make1(rtr), NULL);;
+
+ /* Create a Var referencing the subquery's single output column */
+ newvar = makeVar(1,
+ tle->resno,
+ exprType((Node *) tle->expr),
+ exprTypmod((Node *) tle->expr),
+ exprCollation((Node *) tle->expr),
+ 0);
+
+ /*
+ * Construct the JSON_ARRAYAGG aggregate node. We must manually resolve
+ * the function OID and build a fully analyzed Aggref because we are
+ * bypassing parse analysis.
+ */
+ if (expr->returning &&
+ expr->returning->format->format_type == JS_FORMAT_JSONB)
+ {
+ aggfnoid = expr->absent_on_null ? F_JSONB_AGG_STRICT : F_JSONB_AGG;
+ aggtype = JSONBOID;
+ }
+ else
+ {
+ aggfnoid = expr->absent_on_null ? F_JSON_AGG_STRICT : F_JSON_AGG;
+ aggtype = JSONOID;
+ }
+
+ aggref = makeNode(Aggref);
+ aggref->aggfnoid = aggfnoid;
+ aggref->aggtype = aggtype;
+ aggref->aggcollid = InvalidOid;
+ aggref->inputcollid = exprCollation((Node *) tle->expr);
+ aggref->aggtranstype = InvalidOid;
+ aggref->aggargtypes = list_make1_oid(exprType((Node *) tle->expr));
+ aggref->aggdirectargs = NIL;
+ aggref->args = list_make1(makeTargetEntry((Expr *) newvar, 1, NULL, false));
+ aggref->aggorder = NIL;
+ aggref->aggdistinct = NIL;
+ aggref->aggfilter = NULL;
+ aggref->aggstar = false;
+ aggref->aggvariadic = false;
+ aggref->aggkind = AGGKIND_NORMAL;
+ aggref->aggpresorted = false;
+ aggref->agglevelsup = 0;
+ aggref->aggsplit = AGGSPLIT_SIMPLE;
+ aggref->aggno = -1;
+ aggref->aggtransno = -1;
+ aggref->location = expr->location;
+
+ /*
+ * Attach the aggregate to the outer query's targetlist. The aggregate is
+ * wrapped in a JSCTOR_JSON_ARRAYAGG node to hold the RETURNING and ABSENT
+ * ON NULL formats from the original expression.
+ */
+ newjsctor = makeNode(JsonConstructorExpr);
+ newjsctor->type = JSCTOR_JSON_ARRAYAGG;
+ newjsctor->args = NIL;
+ newjsctor->func = (Expr *) aggref;
+ newjsctor->coercion = copyObject(expr->coercion);
+ newjsctor->returning = copyObject(expr->returning);
+ newjsctor->absent_on_null = expr->absent_on_null;
+ newjsctor->unique = expr->unique;
+ newjsctor->location = expr->location;
+
+ newquery->targetList = list_make1(makeTargetEntry((Expr *) newjsctor,
+ 1,
+ pstrdup("json_arrayagg"),
+ false));
+ newquery->hasAggs = true;
+
+ newsublink->subselect = (Node *) newquery;
+
+ /*
+ * Wrap the entire construct in a COALESCE expression. If the inner query
+ * returns zero rows, the aggregate evaluates to NULL. We use COALESCE to
+ * return an empty JSON array instead.
+ */
+ getTypeInputInfo(aggtype, &typinput, &typioparam);
+ empty_json_const = makeConst(aggtype, -1, InvalidOid, -1,
+ OidInputFunctionCall(typinput, "[]", typioparam, -1),
+ false, false);
+
+ coalesce = makeNode(CoalesceExpr);
+ coalesce->coalescetype = aggtype;
+ coalesce->coalescecollid = InvalidOid;
+ coalesce->args = list_make2(newsublink, empty_json_const);
+ coalesce->location = expr->location;
+
+ return (Node *) coalesce;
+}
+
/*
* var_is_nonnullable: check to see if the Var cannot be NULL
*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index dcfe1acc4c3..06a28ec5e15 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3767,75 +3767,39 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor)
}
/*
- * Transform JSON_ARRAY(query [FORMAT] [RETURNING] [ON NULL]) into
- * (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]) FROM (query) q(a))
+ * Transform JSON_ARRAY(subquery) constructor.
+ *
+ * JSON_ARRAY(subquery) is transformed into a JsonConstructorExpr node of type
+ * JSCTOR_JSON_ARRAY_QUERY. The result is coerced to the target type given by
+ * ctor->output.
*/
static Node *
transformJsonArrayQueryConstructor(ParseState *pstate,
JsonArrayQueryConstructor *ctor)
{
- SubLink *sublink = makeNode(SubLink);
- SelectStmt *select = makeNode(SelectStmt);
- RangeSubselect *range = makeNode(RangeSubselect);
- Alias *alias = makeNode(Alias);
- ResTarget *target = makeNode(ResTarget);
- JsonArrayAgg *agg = makeNode(JsonArrayAgg);
- ColumnRef *colref = makeNode(ColumnRef);
+ SubLink *sublink;
Query *query;
- ParseState *qpstate;
-
- /* Transform query only for counting target list entries. */
- qpstate = make_parsestate(pstate);
-
- query = transformStmt(qpstate, copyObject(ctor->query));
-
- if (count_nonjunk_tlist_entries(query->targetList) != 1)
- ereport(ERROR,
- errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("subquery must return only one column"),
- parser_errposition(pstate, ctor->location));
-
- free_parsestate(qpstate);
-
- colref->fields = list_make2(makeString(pstrdup("q")),
- makeString(pstrdup("a")));
- colref->location = ctor->location;
-
- /*
- * No formatting necessary, so set formatted_expr to be the same as
- * raw_expr.
- */
- agg->arg = makeJsonValueExpr((Expr *) colref, (Expr *) colref,
- ctor->format);
- agg->absent_on_null = ctor->absent_on_null;
- agg->constructor = makeNode(JsonAggConstructor);
- agg->constructor->agg_order = NIL;
- agg->constructor->output = ctor->output;
- agg->constructor->location = ctor->location;
-
- target->name = NULL;
- target->indirection = NIL;
- target->val = (Node *) agg;
- target->location = ctor->location;
-
- alias->aliasname = pstrdup("q");
- alias->colnames = list_make1(makeString(pstrdup("a")));
-
- range->lateral = false;
- range->subquery = ctor->query;
- range->alias = alias;
-
- select->targetList = list_make1(target);
- select->fromClause = list_make1(range);
+ JsonReturning *returning;
+ List *args;
+ sublink = makeNode(SubLink);
sublink->subLinkType = EXPR_SUBLINK;
sublink->subLinkId = 0;
sublink->testexpr = NULL;
sublink->operName = NIL;
- sublink->subselect = (Node *) select;
+ sublink->subselect = (Node *) ctor->query;
sublink->location = ctor->location;
- return transformExprRecurse(pstate, (Node *) sublink);
+ sublink = (SubLink *) transformExprRecurse(pstate, (Node *) sublink);
+
+ query = (Query *) sublink->subselect;
+ args = list_make1(linitial_node(TargetEntry, query->targetList)->expr);
+ returning = transformJsonConstructorOutput(pstate, ctor->output, args);
+
+ return makeJsonConstructorExpr(pstate, JSCTOR_JSON_ARRAY_QUERY,
+ list_make1(sublink), NULL, returning,
+ false, ctor->absent_on_null,
+ ctor->location);
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index f16f1535785..274d3e60d18 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11740,6 +11740,26 @@ get_json_constructor(JsonConstructorExpr *ctor, deparse_context *context,
get_json_agg_constructor(ctor, context, "JSON_ARRAYAGG", false);
return;
}
+ else if (ctor->type == JSCTOR_JSON_ARRAY_QUERY)
+ {
+ /*
+ * Bypass get_sublink_expr to avoid its hardcoded parentheses. We
+ * want JSON_ARRAY(SELECT ...), not JSON_ARRAY((SELECT ...)).
+ */
+
+ SubLink *sublink = linitial_node(SubLink, ctor->args);
+
+ appendStringInfo(buf, "JSON_ARRAY(");
+
+ get_query_def((Query *) sublink->subselect, buf, context->namespaces,
+ NULL, false, context->prettyFlags, context->wrapColumn,
+ context->indentLevel);
+
+ get_json_constructor_options(ctor, buf);
+ appendStringInfoChar(buf, ')');
+
+ return;
+ }
switch (ctor->type)
{
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 384df50c80a..8435e76105b 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1713,13 +1713,14 @@ typedef struct JsonValueExpr
typedef enum JsonConstructorType
{
- JSCTOR_JSON_OBJECT = 1,
- JSCTOR_JSON_ARRAY = 2,
- JSCTOR_JSON_OBJECTAGG = 3,
- JSCTOR_JSON_ARRAYAGG = 4,
- JSCTOR_JSON_PARSE = 5,
- JSCTOR_JSON_SCALAR = 6,
- JSCTOR_JSON_SERIALIZE = 7,
+ JSCTOR_JSON_OBJECT,
+ JSCTOR_JSON_ARRAY,
+ JSCTOR_JSON_ARRAY_QUERY,
+ JSCTOR_JSON_OBJECTAGG,
+ JSCTOR_JSON_ARRAYAGG,
+ JSCTOR_JSON_PARSE,
+ JSCTOR_JSON_SCALAR,
+ JSCTOR_JSON_SERIALIZE,
} JsonConstructorType;
/*
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index c7b9e575445..c7c6c6413e3 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -724,6 +724,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT
[[{ "a" : 123 }]]
(1 row)
+-- JSON_ARRAY(subquery)
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i));
json_array
------------
@@ -757,6 +758,31 @@ SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE));
[true]
(1 row)
+-- JSON_ARRAY(subquery) with empty result set
+SELECT JSON_ARRAY(SELECT 1 WHERE FALSE);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4);
+ json_array
+------------
+ []
+(1 row)
+
+-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause
+SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a)
+WHERE JSON_ARRAY(
+ SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a
+ RETURNING jsonb
+) = '[]'::jsonb;
+ a
+---
+
+ 4
+(2 rows)
+
-- Should fail
SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i));
ERROR: subquery must return only one column
@@ -1093,7 +1119,7 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING
QUERY PLAN
---------------------------------------------------------------------
Result
- Output: (InitPlan expr_1).col1
+ Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb)
InitPlan expr_1
-> Aggregate
Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
@@ -1105,9 +1131,67 @@ CREATE VIEW json_array_subquery_view AS
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
\sv json_array_subquery_view
CREATE OR REPLACE VIEW public.json_array_subquery_view AS
- SELECT ( SELECT JSON_ARRAYAGG(q.a RETURNING jsonb) AS "json_arrayagg"
- FROM ( SELECT foo.i
- FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array"
+ SELECT JSON_ARRAY( SELECT foo.i
+ FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) RETURNING jsonb) AS "json_array"
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb);
+ QUERY PLAN
+---------------------------------------------------------------------
+ Result
+ Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb)
+ InitPlan expr_1
+ -> Aggregate
+ Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
+ -> Limit
+ Output: "*VALUES*".column1
+ -> Sort
+ Output: "*VALUES*".column1
+ Sort Key: "*VALUES*".column1
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1
+(12 rows)
+
+CREATE OR REPLACE VIEW json_array_subquery_view AS
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb);
+\sv json_array_subquery_view
+CREATE OR REPLACE VIEW public.json_array_subquery_view AS
+ SELECT JSON_ARRAY( SELECT foo.i
+ FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)
+ ORDER BY foo.i
+ LIMIT 3 RETURNING jsonb) AS "json_array"
+DROP VIEW json_array_subquery_view;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a)
+WHERE JSON_ARRAY(
+ SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a
+ RETURNING jsonb
+) = '[]'::jsonb;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1
+ Filter: (COALESCE((SubPlan expr_1), '[]'::jsonb) = '[]'::jsonb)
+ SubPlan expr_1
+ -> Aggregate
+ Output: JSON_ARRAYAGG("*VALUES*_1".column1 RETURNING jsonb)
+ -> Values Scan on "*VALUES*_1"
+ Output: "*VALUES*_1".column1
+ Filter: ("*VALUES*_1".column1 = "*VALUES*".column1)
+(9 rows)
+
+CREATE VIEW json_array_subquery_view AS
+SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a)
+WHERE JSON_ARRAY(
+ SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a
+ RETURNING jsonb
+) = '[]'::jsonb;
+\sv json_array_subquery_view
+CREATE OR REPLACE VIEW public.json_array_subquery_view AS
+ SELECT a
+ FROM ( VALUES (1), (2), (NULL::integer), (4)) t1(a)
+ WHERE JSON_ARRAY( SELECT t2.b
+ FROM ( VALUES (1), (2), (3)) t2(b)
+ WHERE t2.b = t1.a RETURNING jsonb) = '[]'::jsonb
DROP VIEW json_array_subquery_view;
-- IS JSON predicate
SELECT NULL IS JSON;
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 343d344d270..1033bf4287a 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -193,6 +193,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text));
SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text));
SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT JSON);
+-- JSON_ARRAY(subquery)
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i));
SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i));
SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) RETURNING jsonb);
@@ -201,6 +202,17 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL)
SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1), (NULL), (2)) foo(i) ORDER BY i);
SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE));
+-- JSON_ARRAY(subquery) with empty result set
+SELECT JSON_ARRAY(SELECT 1 WHERE FALSE);
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4);
+
+-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause
+SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a)
+WHERE JSON_ARRAY(
+ SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a
+ RETURNING jsonb
+) = '[]'::jsonb;
+
-- Should fail
SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i));
SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i));
@@ -384,6 +396,32 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING
\sv json_array_subquery_view
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb);
+
+CREATE OR REPLACE VIEW json_array_subquery_view AS
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb);
+
+\sv json_array_subquery_view
+
+DROP VIEW json_array_subquery_view;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a)
+WHERE JSON_ARRAY(
+ SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a
+ RETURNING jsonb
+) = '[]'::jsonb;
+
+CREATE VIEW json_array_subquery_view AS
+SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a)
+WHERE JSON_ARRAY(
+ SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a
+ RETURNING jsonb
+) = '[]'::jsonb;
+
+\sv json_array_subquery_view
+
DROP VIEW json_array_subquery_view;
-- IS JSON predicate
--
2.39.5 (Apple Git-154)
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], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
In-Reply-To: <CAMbWs4-NuYP+0Zq9=Ufwy5GcM0XQnEqWjJFucaRGO4EQgiCcow@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