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: Thu, 16 Apr 2026 15:04:49 +0900
Message-ID: <CAMbWs4-ra9mkBhUs2kmB5KxWuo80oAA33gZ=8JmRxmG3qGNxUg@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs4-NuYP+0Zq9=Ufwy5GcM0XQnEqWjJFucaRGO4EQgiCcow@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>
<CAMbWs4-NuYP+0Zq9=Ufwy5GcM0XQnEqWjJFucaRGO4EQgiCcow@mail.gmail.com>
On Tue, Mar 3, 2026 at 11:32 PM Richard Guo <[email protected]> wrote:
> 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.
After a second look at this approach, I don't like it very much. It
manually constructed the new querytree, including Aggref,
RangeTblEntry, and JsonConstructorExpr nodes, during planning,
bypassing parse analysis entirely. This is essentially repeating the
parser's work by hand in the planner, which is fragile and prone to
failing to handle all cases correctly.
Maybe a simpler way is to keep the JSON_ARRAYAGG rewrite trick in the
parser, as the current master does, but wrap the result in a COALESCE
to handle the empty-set case. We can preserve a copy of the user's
original subquery in a new field of JsonConstructorExpr, and then
ruleutils.c can use this field to deparse the original
JSON_ARRAY(SELECT ...) syntax for view definitions. You may think
this would introduce extra transform work, but it wouldn't: the
current master already transforms the original subquery to validate
the single-column constraint, then throws the result away. We simply
keep it instead.
I tried this idea and ended up with the attached.
- Richard
Attachments:
[application/octet-stream] v4-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch (20.9K, 2-v4-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch)
download | inline diff:
From 2883d47ac0ccd0f14e8fb1f84d334b6e3331a5c7 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Mon, 2 Mar 2026 12:11:00 +0900
Subject: [PATCH v4] 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 introducing a new
JSCTOR_JSON_ARRAY_QUERY constructor type. The parser builds the
executable form --- a COALESCE-wrapped JSON_ARRAYAGG subquery --- from
raw parse nodes via transformExprRecurse, and stores it in the func
field. The original transformed Query is kept in a new orig_query
field so that ruleutils.c can deparse the original syntax for views.
During planning, eval_const_expressions replaces the node with the
pre-built func expression.
---
doc/src/sgml/func/func-json.sgml | 3 +-
src/backend/nodes/nodeFuncs.c | 11 +++
src/backend/optimizer/util/clauses.c | 15 +++-
src/backend/parser/parse_expr.c | 111 +++++++++++++++++++++----
src/backend/utils/adt/ruleutils.c | 15 ++++
src/include/nodes/primnodes.h | 14 ++--
src/test/regress/expected/sqljson.out | 113 +++++++++++++++++++++++++-
src/test/regress/sql/sqljson.sql | 49 +++++++++++
8 files changed, 304 insertions(+), 27 deletions(-)
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 4cd338fe6e3..3d97e2b5375 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/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c0b880ec233..cca0a6c0a64 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1002,8 +1002,16 @@ exprCollation(const Node *expr)
{
const JsonConstructorExpr *ctor = (const JsonConstructorExpr *) expr;
+ /*
+ * Collation comes from coercion if present, otherwise from
+ * func. The func fallback is needed in cases where func
+ * already produces the final output type and no coercion is
+ * needed (cf. the JSCTOR_JSON_ARRAY_QUERY case).
+ */
if (ctor->coercion)
coll = exprCollation((Node *) ctor->coercion);
+ else if (ctor->func)
+ coll = exprCollation((Node *) ctor->func);
else
coll = InvalidOid;
}
@@ -1264,8 +1272,11 @@ exprSetCollation(Node *expr, Oid collation)
{
JsonConstructorExpr *ctor = (JsonConstructorExpr *) expr;
+ /* See comment in exprCollation() */
if (ctor->coercion)
exprSetCollation((Node *) ctor->coercion, collation);
+ else if (ctor->func)
+ exprSetCollation((Node *) ctor->func, collation);
else
Assert(!OidIsValid(collation)); /* result is always a
* json[b] type */
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index fcf6d7fff2a..cd86311bb0b 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3244,7 +3244,6 @@ eval_const_expressions_mutator(Node *node,
}
break;
}
-
case T_JsonValueExpr:
{
JsonValueExpr *jve = (JsonValueExpr *) node;
@@ -3268,7 +3267,21 @@ eval_const_expressions_mutator(Node *node,
(Expr *) formatted_expr,
copyObject(jve->format));
}
+ case T_JsonConstructorExpr:
+ {
+ JsonConstructorExpr *jce = (JsonConstructorExpr *) node;
+ /*
+ * JSCTOR_JSON_ARRAY_QUERY carries a pre-built executable form
+ * in its func field (a COALESCE-wrapped JSON_ARRAYAGG
+ * subquery, constructed during parse analysis). Replace the
+ * node with that expression and continue simplifying.
+ */
+ if (jce->type == JSCTOR_JSON_ARRAY_QUERY)
+ return eval_const_expressions_mutator((Node *) jce->func,
+ context);
+ }
+ break;
case T_SubPlan:
case T_AlternativeSubPlan:
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f535f3b9351..c3c7aa29720 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3792,24 +3792,53 @@ 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 node carries:
+ *
+ * - func: the executable form, which is a COALESCE expression wrapping a
+ * JSON_ARRAYAGG subquery:
+ *
+ * COALESCE((SELECT JSON_ARRAYAGG(a) FROM (subquery) q(a)), '[]')
+ *
+ * The COALESCE ensures that an empty result set produces '[]' rather than
+ * NULL, per the SQL/JSON standard.
+ *
+ * - orig_query: the transformed Query of the user's original subquery, so
+ * that ruleutils.c can deparse the original JSON_ARRAY(SELECT ...) syntax
+ * for view definitions.
*/
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);
Query *query;
ParseState *qpstate;
+ SubLink *sublink;
+ SelectStmt *select;
+ RangeSubselect *range;
+ Alias *alias;
+ ResTarget *target;
+ JsonArrayAgg *agg;
+ ColumnRef *colref;
+ Node *exec_expr;
+ CoalesceExpr *coalesce;
+ Const *empty_const;
+ Oid result_type;
+ Oid typinput;
+ Oid typioparam;
+ int16 typlen;
+ bool typbyval;
+ JsonReturning *returning;
+ List *args;
+ Node *result;
- /* Transform query only for counting target list entries. */
+ /*
+ * Transform a copy of the subquery to validate the single-column
+ * constraint and to obtain the transformed Query for deparsing. This
+ * uses a private ParseState so it doesn't affect the main parse context.
+ */
qpstate = make_parsestate(pstate);
query = transformStmt(qpstate, copyObject(ctor->query));
@@ -3822,14 +3851,20 @@ transformJsonArrayQueryConstructor(ParseState *pstate,
free_parsestate(qpstate);
+ /*
+ * Build the executable form by constructing query:
+ *
+ * (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING]) FROM (subquery) q(a))
+ *
+ * ... using raw parse tree nodes, then transforming via
+ * transformExprRecurse.
+ */
+ colref = makeNode(ColumnRef);
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 = makeNode(JsonArrayAgg);
agg->arg = makeJsonValueExpr((Expr *) colref, (Expr *) colref,
ctor->format);
agg->absent_on_null = ctor->absent_on_null;
@@ -3838,21 +3873,26 @@ transformJsonArrayQueryConstructor(ParseState *pstate,
agg->constructor->output = ctor->output;
agg->constructor->location = ctor->location;
+ target = makeNode(ResTarget);
target->name = NULL;
target->indirection = NIL;
target->val = (Node *) agg;
target->location = ctor->location;
+ alias = makeNode(Alias);
alias->aliasname = pstrdup("q");
alias->colnames = list_make1(makeString(pstrdup("a")));
+ range = makeNode(RangeSubselect);
range->lateral = false;
range->subquery = ctor->query;
range->alias = alias;
+ select = makeNode(SelectStmt);
select->targetList = list_make1(target);
select->fromClause = list_make1(range);
+ sublink = makeNode(SubLink);
sublink->subLinkType = EXPR_SUBLINK;
sublink->subLinkId = 0;
sublink->testexpr = NULL;
@@ -3860,7 +3900,48 @@ transformJsonArrayQueryConstructor(ParseState *pstate,
sublink->subselect = (Node *) select;
sublink->location = ctor->location;
- return transformExprRecurse(pstate, (Node *) sublink);
+ exec_expr = transformExprRecurse(pstate, (Node *) sublink);
+
+ /*
+ * Wrap in COALESCE so that an empty result set produces '[]' rather than
+ * NULL. The empty-array constant is created in the output type so that
+ * the COALESCE arguments have consistent types.
+ */
+ result_type = exprType(exec_expr);
+ getTypeInputInfo(result_type, &typinput, &typioparam);
+ get_typlenbyval(result_type, &typlen, &typbyval);
+
+ empty_const = makeConst(result_type,
+ -1,
+ exprCollation(exec_expr),
+ (int) typlen,
+ OidInputFunctionCall(typinput, "[]",
+ typioparam, -1),
+ false,
+ typbyval);
+
+ coalesce = makeNode(CoalesceExpr);
+ coalesce->coalescetype = result_type;
+ coalesce->coalescecollid = exprCollation(exec_expr);
+ coalesce->args = list_make2(exec_expr, empty_const);
+ coalesce->location = ctor->location;
+
+ /*
+ * Build the JSCTOR_JSON_ARRAY_QUERY node. The COALESCE goes in func as
+ * the executable form; during planning, eval_const_expressions replaces
+ * the entire node with func. The transformed Query is stored in
+ * orig_query so that ruleutils.c can deparse the original syntax.
+ */
+ args = list_make1(linitial_node(TargetEntry, query->targetList)->expr);
+ returning = transformJsonConstructorOutput(pstate, ctor->output, args);
+
+ result = makeJsonConstructorExpr(pstate, JSCTOR_JSON_ARRAY_QUERY,
+ NIL, (Expr *) coalesce, returning,
+ false, ctor->absent_on_null,
+ ctor->location);
+ ((JsonConstructorExpr *) result)->orig_query = (Node *) query;
+
+ return result;
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 090e8cc28c1..2ddd70372b1 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12281,6 +12281,21 @@ 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)
+ {
+ Query *query = castNode(Query, ctor->orig_query);
+
+ appendStringInfo(buf, "JSON_ARRAY(");
+
+ get_query_def(query, 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 6dfc946c20b..0c69e31da54 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1715,12 +1715,13 @@ 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_ARRAY,
+ JSCTOR_JSON_ARRAY_QUERY,
+ JSCTOR_JSON_OBJECTAGG,
+ JSCTOR_JSON_ARRAYAGG,
+ JSCTOR_JSON_PARSE,
+ JSCTOR_JSON_SCALAR,
+ JSCTOR_JSON_SERIALIZE,
} JsonConstructorType;
/*
@@ -1735,6 +1736,7 @@ typedef struct JsonConstructorExpr
Expr *func; /* underlying json[b]_xxx() function call */
Expr *coercion; /* coercion to RETURNING type */
JsonReturning *returning; /* RETURNING clause */
+ Node *orig_query; /* JSON_ARRAY query for deparsing; not walked */
bool absent_on_null; /* ABSENT ON NULL? */
bool unique; /* WITH UNIQUE KEYS? (JSON_OBJECT[AGG] only) */
ParseLoc location;
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index f3be69838bf..a14936a4e6e 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,88 @@ 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;
+-- JSON_ARRAY(subquery) with RETURNING text
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Result
+ Output: COALESCE((InitPlan expr_1).col1, '[]'::text)
+ InitPlan expr_1
+ -> Aggregate
+ Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING text)
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1
+(7 rows)
+
+CREATE VIEW json_array_subquery_view AS
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text);
+\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) RETURNING text) AS "json_array"
DROP VIEW json_array_subquery_view;
-- Test mutability of JSON_OBJECTAGG, JSON_ARRAYAGG, JSON_ARRAY, JSON_OBJECT
create type comp1 as (a int, b date);
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 5b2c4661556..00ecf6161bf 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,43 @@ 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;
+
+-- JSON_ARRAY(subquery) with RETURNING text
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text);
+
+CREATE VIEW json_array_subquery_view AS
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text);
+
+\sv json_array_subquery_view
+
DROP VIEW json_array_subquery_view;
-- Test mutability of JSON_OBJECTAGG, JSON_ARRAYAGG, JSON_ARRAY, JSON_OBJECT
--
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-ra9mkBhUs2kmB5KxWuo80oAA33gZ=8JmRxmG3qGNxUg@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