Skip to content

Commit 03ec203

Browse files
amitlanamitlan
committed
SQL/JSON: Correctly enforce the default ON EMPTY behavior
Currently, when the ON EMPTY clause is not present, the ON ERROR clause (implicit or explicit) dictates the behavior when jsonpath evaluation in ExecEvalJsonExprPath() results in an empty sequence. That is an oversight in the commit 6185c97. This commit fixes things so that a NULL is returned instead in that case which is the default behavior when the ON EMPTY clause is not present. Reported-by: Markus Winand Discussion: https://postgr.es/m/F7DD1442-265C-4220-A603-CB0DEB77E91D%40winand.at
1 parent 0f271e8 commit 03ec203

File tree

5 files changed

+64
-40
lines changed

5 files changed

+64
-40
lines changed

‎src/backend/parser/parse_expr.c

+17-10
Original file line numberDiff line numberDiff line change
@@ -4418,11 +4418,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
44184418

44194419
coerceJsonExprOutput(pstate, jsexpr);
44204420

4421-
if (func->on_empty)
4422-
jsexpr->on_empty = transformJsonBehavior(pstate,
4423-
func->on_empty,
4424-
JSON_BEHAVIOR_NULL,
4425-
jsexpr->returning);
4421+
/* Assume NULL ON EMPTY when ON EMPTY is not specified. */
4422+
jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
4423+
JSON_BEHAVIOR_NULL,
4424+
jsexpr->returning);
4425+
/* Assume NULL ON ERROR when ON ERROR is not specified. */
44264426
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
44274427
JSON_BEHAVIOR_NULL,
44284428
jsexpr->returning);
@@ -4448,11 +4448,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
44484448

44494449
coerceJsonExprOutput(pstate, jsexpr);
44504450

4451-
if (func->on_empty)
4452-
jsexpr->on_empty = transformJsonBehavior(pstate,
4453-
func->on_empty,
4454-
JSON_BEHAVIOR_NULL,
4455-
jsexpr->returning);
4451+
/* Assume NULL ON EMPTY when ON EMPTY is not specified. */
4452+
jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
4453+
JSON_BEHAVIOR_NULL,
4454+
jsexpr->returning);
4455+
/* Assume NULL ON ERROR when ON ERROR is not specified. */
44564456
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
44574457
JSON_BEHAVIOR_NULL,
44584458
jsexpr->returning);
@@ -4464,6 +4464,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
44644464
jsexpr->returning->typid = exprType(jsexpr->formatted_expr);
44654465
jsexpr->returning->typmod = -1;
44664466
}
4467+
4468+
/*
4469+
* Assume EMPTY ON ERROR when ON ERROR is not specified.
4470+
*
4471+
* ON EMPTY cannot be specified at the top level but it can be for
4472+
* the individual columns.
4473+
*/
44674474
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
44684475
JSON_BEHAVIOR_EMPTY,
44694476
jsexpr->returning);

‎src/test/regress/expected/sqljson_jsontable.out

+14-5
Original file line numberDiff line numberDiff line change
@@ -219,17 +219,17 @@ FROM json_table_test vals
219219

220220
-- Test using casts in DEFAULT .. ON ERROR expression
221221
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
222-
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
222+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON EMPTY));
223223
js1
224224
--------
225225
"foo1"
226226
(1 row)
227227

228228
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
229-
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
229+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON EMPTY));
230230
ERROR: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
231231
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
232-
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
232+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON EMPTY));
233233
js1
234234
------
235235
foo1
@@ -243,7 +243,7 @@ SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
243243
(1 row)
244244

245245
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
246-
COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON ERROR));
246+
COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON EMPTY));
247247
js1
248248
-----
249249
{1}
@@ -885,7 +885,16 @@ SELECT sub.* FROM s,
885885
xx int path '$.c',
886886
NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
887887
)) sub;
888-
ERROR: no SQL/JSON item found for specified path of column "z21"
888+
xx | z21
889+
----+------
890+
3 |
891+
3 | 234
892+
3 | 2345
893+
10 | 32
894+
10 | 134
895+
10 | 1345
896+
(6 rows)
897+
889898
-- Parent columns xx1, xx appear before NESTED ones
890899
SELECT sub.* FROM s,
891900
(VALUES (23)) x(x), generate_series(13, 13) y,

‎src/test/regress/expected/sqljson_queryfuncs.out

+20-12
Original file line numberDiff line numberDiff line change
@@ -362,11 +362,15 @@ SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
362362
error
363363
(1 row)
364364

365-
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
366-
ERROR: no SQL/JSON item found for specified path
365+
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR); -- NULL ON EMPTY
366+
json_value
367+
------------
368+
369+
(1 row)
370+
367371
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
368372
ERROR: no SQL/JSON item found for specified path
369-
SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
373+
SELECT JSON_VALUE(jsonb '1', 'strict $.*' DEFAULT 2 ON ERROR);
370374
json_value
371375
------------
372376
2
@@ -375,10 +379,10 @@ SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
375379
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
376380
json_value
377381
------------
378-
2
382+
379383
(1 row)
380384

381-
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
385+
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY);
382386
json_value
383387
------------
384388
2
@@ -773,8 +777,12 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
773777
ERROR: no SQL/JSON item found for specified path
774778
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
775779
ERROR: no SQL/JSON item found for specified path
776-
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
777-
ERROR: no SQL/JSON item found for specified path
780+
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR); -- NULL ON EMPTY
781+
json_query
782+
------------
783+
784+
(1 row)
785+
778786
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
779787
ERROR: JSON path expression in JSON_QUERY should return single item without wrapper
780788
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);
10321040

10331041
(1 row)
10341042

1035-
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
1043+
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON EMPTY ERROR ON ERROR);
10361044
ERROR: no SQL/JSON item found for specified path
10371045
-- Test timestamptz passing and output
10381046
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) ...
12231231
DROP TABLE test_jsonb_mutability;
12241232
DROP FUNCTION ret_setint;
12251233
CREATE DOMAIN queryfuncs_test_domain AS text CHECK (value <> 'foo');
1226-
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON ERROR);
1234+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON EMPTY);
12271235
ERROR: value for domain queryfuncs_test_domain violates check constraint "queryfuncs_test_domain_check"
1228-
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
1236+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
12291237
json_value
12301238
------------
12311239
foo1
12321240
(1 row)
12331241

1234-
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
1242+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON EMPTY);
12351243
json_value
12361244
------------
12371245
"foo1"
12381246
(1 row)
12391247

1240-
SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
1248+
SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
12411249
json_value
12421250
------------
12431251
foo1

‎src/test/regress/sql/sqljson_jsontable.sql

+4-4
Original file line numberDiff line numberDiff line change
@@ -118,19 +118,19 @@ FROM json_table_test vals
118118

119119
-- Test using casts in DEFAULT .. ON ERROR expression
120120
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
121-
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
121+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON EMPTY));
122122

123123
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
124-
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
124+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON EMPTY));
125125

126126
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
127-
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
127+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON EMPTY));
128128

129129
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
130130
COLUMNS (js1 jsonb_test_domain PATH '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
131131

132132
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
133-
COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON ERROR));
133+
COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON EMPTY));
134134

135135
-- JSON_TABLE: Test backward parsing
136136

‎src/test/regress/sql/sqljson_queryfuncs.sql

+9-9
Original file line numberDiff line numberDiff line change
@@ -87,11 +87,11 @@ SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
8787
SELECT JSON_VALUE(jsonb '1', '$.a');
8888
SELECT JSON_VALUE(jsonb '1', 'strict $.a' ERROR ON ERROR);
8989
SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
90-
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
90+
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR); -- NULL ON EMPTY
9191
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
92-
SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
92+
SELECT JSON_VALUE(jsonb '1', 'strict $.*' DEFAULT 2 ON ERROR);
9393
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
94-
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
94+
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY);
9595
SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
9696
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
9797
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);
224224
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
225225
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
226226
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
227-
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
227+
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR); -- NULL ON EMPTY
228228

229229
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
230230
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
304304
-- Extension: domain types returning
305305
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
306306
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
307-
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
307+
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON EMPTY ERROR ON ERROR);
308308

309309
-- Test timestamptz passing and output
310310
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;
412412
DROP FUNCTION ret_setint;
413413

414414
CREATE DOMAIN queryfuncs_test_domain AS text CHECK (value <> 'foo');
415-
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON ERROR);
416-
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
417-
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
418-
SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON ERROR);
415+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON EMPTY);
416+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
417+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ON EMPTY);
418+
SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
419419

420420
-- Check the cases where a coercion-related expression is masking an
421421
-- unsupported expressions

0 commit comments

Comments
 (0)