aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/parser/parse_expr.c27
-rw-r--r--src/test/regress/expected/sqljson_jsontable.out19
-rw-r--r--src/test/regress/expected/sqljson_queryfuncs.out32
-rw-r--r--src/test/regress/sql/sqljson_jsontable.sql8
-rw-r--r--src/test/regress/sql/sqljson_queryfuncs.sql18
5 files changed, 64 insertions, 40 deletions
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4c95986c312..00cd7358ebb 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4418,11 +4418,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
coerceJsonExprOutput(pstate, jsexpr);
- if (func->on_empty)
- jsexpr->on_empty = transformJsonBehavior(pstate,
- func->on_empty,
- JSON_BEHAVIOR_NULL,
- jsexpr->returning);
+ /* Assume NULL ON EMPTY when ON EMPTY is not specified. */
+ jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
+ JSON_BEHAVIOR_NULL,
+ jsexpr->returning);
+ /* Assume NULL ON ERROR when ON ERROR is not specified. */
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
JSON_BEHAVIOR_NULL,
jsexpr->returning);
@@ -4448,11 +4448,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
coerceJsonExprOutput(pstate, jsexpr);
- if (func->on_empty)
- jsexpr->on_empty = transformJsonBehavior(pstate,
- func->on_empty,
- JSON_BEHAVIOR_NULL,
- jsexpr->returning);
+ /* Assume NULL ON EMPTY when ON EMPTY is not specified. */
+ jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
+ JSON_BEHAVIOR_NULL,
+ jsexpr->returning);
+ /* Assume NULL ON ERROR when ON ERROR is not specified. */
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
JSON_BEHAVIOR_NULL,
jsexpr->returning);
@@ -4464,6 +4464,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
jsexpr->returning->typid = exprType(jsexpr->formatted_expr);
jsexpr->returning->typmod = -1;
}
+
+ /*
+ * Assume EMPTY ON ERROR when ON ERROR is not specified.
+ *
+ * ON EMPTY cannot be specified at the top level but it can be for
+ * the individual columns.
+ */
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
JSON_BEHAVIOR_EMPTY,
jsexpr->returning);
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 9f649483cec..cee90cead13 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -219,17 +219,17 @@ FROM json_table_test vals
-- Test using casts in DEFAULT .. ON ERROR expression
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
- COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
+ COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON EMPTY));
js1
--------
"foo1"
(1 row)
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
- COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
+ COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON EMPTY));
ERROR: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
- COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
+ COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON EMPTY));
js1
------
foo1
@@ -243,7 +243,7 @@ SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
(1 row)
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
- COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON ERROR));
+ COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON EMPTY));
js1
-----
{1}
@@ -885,7 +885,16 @@ SELECT sub.* FROM s,
xx int path '$.c',
NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
)) sub;
-ERROR: no SQL/JSON item found for specified path of column "z21"
+ xx | z21
+----+------
+ 3 |
+ 3 | 234
+ 3 | 2345
+ 10 | 32
+ 10 | 134
+ 10 | 1345
+(6 rows)
+
-- Parent columns xx1, xx appear before NESTED ones
SELECT sub.* FROM s,
(VALUES (23)) x(x), generate_series(13, 13) y,
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 98117b346d4..9cb250a27a7 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -362,11 +362,15 @@ SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
error
(1 row)
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
-ERROR: no SQL/JSON item found for specified path
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR); -- NULL ON EMPTY
+ json_value
+------------
+
+(1 row)
+
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
ERROR: no SQL/JSON item found for specified path
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'strict $.*' DEFAULT 2 ON ERROR);
json_value
------------
2
@@ -375,10 +379,10 @@ SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
json_value
------------
- 2
+
(1 row)
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY);
json_value
------------
2
@@ -773,8 +777,12 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
ERROR: no SQL/JSON item found for specified path
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
ERROR: no SQL/JSON item found for specified path
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
-ERROR: no SQL/JSON item found for specified path
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR); -- NULL ON EMPTY
+ json_query
+------------
+
+(1 row)
+
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
ERROR: JSON path expression in JSON_QUERY should return single item without wrapper
HINT: Use WITH WRAPPER clause to wrap SQL/JSON items into array.
@@ -1032,7 +1040,7 @@ SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
(1 row)
-SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON EMPTY ERROR ON ERROR);
ERROR: no SQL/JSON item found for specified path
-- Test timestamptz passing and output
SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
@@ -1223,21 +1231,21 @@ LINE 1: SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT (SELECT 1) ...
DROP TABLE test_jsonb_mutability;
DROP FUNCTION ret_setint;
CREATE DOMAIN queryfuncs_test_domain AS text CHECK (value <> 'foo');
-SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON EMPTY);
ERROR: value for domain queryfuncs_test_domain violates check constraint "queryfuncs_test_domain_check"
-SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
json_value
------------
foo1
(1 row)
-SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON EMPTY);
json_value
------------
"foo1"
(1 row)
-SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
json_value
------------
foo1
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index f8f996f9352..a1f924146e0 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -118,19 +118,19 @@ FROM json_table_test vals
-- Test using casts in DEFAULT .. ON ERROR expression
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
- COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
+ COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON EMPTY));
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
- COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
+ COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON EMPTY));
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
- COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
+ COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON EMPTY));
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
COLUMNS (js1 jsonb_test_domain PATH '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
- COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON ERROR));
+ COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON EMPTY));
-- JSON_TABLE: Test backward parsing
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index d9dbb1ceaac..dc6380141b8 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -87,11 +87,11 @@ SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
SELECT JSON_VALUE(jsonb '1', '$.a');
SELECT JSON_VALUE(jsonb '1', 'strict $.a' ERROR ON ERROR);
SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR); -- NULL ON EMPTY
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'strict $.*' DEFAULT 2 ON ERROR);
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY);
SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
@@ -224,7 +224,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR); -- NULL ON EMPTY
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
@@ -304,7 +304,7 @@ SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb
-- Extension: domain types returning
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
-SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON EMPTY ERROR ON ERROR);
-- Test timestamptz passing and output
SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
@@ -412,10 +412,10 @@ DROP TABLE test_jsonb_mutability;
DROP FUNCTION ret_setint;
CREATE DOMAIN queryfuncs_test_domain AS text CHECK (value <> 'foo');
-SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON ERROR);
-SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
-SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
-SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON EMPTY);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
+SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON EMPTY);
+SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
-- Check the cases where a coercion-related expression is masking an
-- unsupported expressions
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-22 22:01:50 +0000