Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit03ec203

Browse files
committed
SQL/JSON: Correctly enforce the default ON EMPTY behavior
Currently, when the ON EMPTY clause is not present, the ON ERRORclause (implicit or explicit) dictates the behavior when jsonpathevaluation in ExecEvalJsonExprPath() results in an empty sequence.That is an oversight in the commit6185c97.This commit fixes things so that a NULL is returned instead in thatcase which is the default behavior when the ON EMPTY clause is notpresent.Reported-by: Markus WinandDiscussion:https://postgr.es/m/F7DD1442-265C-4220-A603-CB0DEB77E91D%40winand.at
1 parent0f271e8 commit03ec203

File tree

5 files changed

+64
-40
lines changed

5 files changed

+64
-40
lines changed

‎src/backend/parser/parse_expr.c

Lines changed: 17 additions & 10 deletions
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

Lines changed: 14 additions & 5 deletions
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 ONERROR));
222+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ONEMPTY));
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 ONERROR));
229+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ONEMPTY));
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 ONERROR));
232+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ONEMPTY));
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[] ONERROR));
246+
COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ONEMPTY));
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

Lines changed: 20 additions & 12 deletions
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' ONERROR);
385+
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ONEMPTY);
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 ONEMPTY ERROR ONERROR);
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 ONERROR);
1234+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ONEMPTY);
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 ONERROR);
1236+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ONEMPTY);
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 ONERROR);
1242+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT '"foo1"'::jsonb::text ONEMPTY);
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 ONERROR);
1248+
SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ONEMPTY);
12411249
json_value
12421250
------------
12431251
foo1

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

Lines changed: 4 additions & 4 deletions
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_domainPATH'$.a2' DEFAULT'"foo1"'::jsonb::textONERROR));
121+
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'"foo1"'::jsonb::textONEMPTY));
122122

123123
SELECT*FROM JSON_TABLE(jsonb'{"d1": "H"}','$'
124-
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'foo'::jsonb_test_domainONERROR));
124+
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'foo'::jsonb_test_domainONEMPTY));
125125

126126
SELECT*FROM JSON_TABLE(jsonb'{"d1": "H"}','$'
127-
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'foo1'::jsonb_test_domainONERROR));
127+
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'foo1'::jsonb_test_domainONEMPTY));
128128

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

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

135135
-- JSON_TABLE: Test backward parsing
136136

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

Lines changed: 9 additions & 9 deletions
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' ERRORON ERROR);
8989
SELECT JSON_VALUE(jsonb'1','strict $.a' DEFAULT'error'ON ERROR);
90-
SELECT JSON_VALUE(jsonb'1','lax $.a' ERRORON ERROR);
90+
SELECT JSON_VALUE(jsonb'1','lax $.a' ERRORON ERROR);-- NULL ON EMPTY
9191
SELECT JSON_VALUE(jsonb'1','lax $.a' ERRORON EMPTY ERRORON ERROR);
92-
SELECT JSON_VALUE(jsonb'1','strict $.a' DEFAULT2ON ERROR);
92+
SELECT JSON_VALUE(jsonb'1','strict $.*' DEFAULT2ON ERROR);
9393
SELECT JSON_VALUE(jsonb'1','lax $.a' DEFAULT2ON ERROR);
94-
SELECT JSON_VALUE(jsonb'1','lax $.a' DEFAULT'2'ONERROR);
94+
SELECT JSON_VALUE(jsonb'1','lax $.a' DEFAULT'2'ONEMPTY);
9595
SELECT JSON_VALUE(jsonb'1','lax $.a'NULLON 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' ERRORON EMPTY DEFAULT'3'ON ERROR);
@@ -224,7 +224,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
224224
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON EMPTY EMPTY ARRAYON ERROR);
225225
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON EMPTY EMPTY OBJECTON ERROR);
226226
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON EMPTY ERRORON ERROR);
227-
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON ERROR);
227+
SELECT JSON_QUERY(jsonb'[]','$[*]' ERRORON ERROR);-- NULL ON EMPTY
228228

229229
SELECT JSON_QUERY(jsonb'[1,2]','$[*]' ERRORON 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 ERRORON ERROR);
307+
SELECT JSON_QUERY(jsonb'{"a": 1}','$.b' RETURNING sqljsonb_int_not_null ERRORONEMPTY ERRORONERROR);
308308

309309
-- Test timestamptz passing and output
310310
SELECT JSON_QUERY(jsonb'null','$ts' PASSINGtimestamptz'2018-02-21 12:34:56 +10'AS ts);
@@ -412,10 +412,10 @@ DROP TABLE test_jsonb_mutability;
412412
DROPFUNCTION ret_setint;
413413

414414
CREATEDOMAINqueryfuncs_test_domainAStextCHECK (value<>'foo');
415-
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'foo'::queryfuncs_test_domainONERROR);
416-
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'foo1'::queryfuncs_test_domainONERROR);
417-
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'"foo1"'::jsonb::textONERROR);
418-
SELECT JSON_VALUE(jsonb'{"d1": "foo"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'foo1'::queryfuncs_test_domainONERROR);
415+
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'foo'::queryfuncs_test_domainONEMPTY);
416+
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'foo1'::queryfuncs_test_domainONEMPTY);
417+
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'"foo1"'::jsonb::textONEMPTY);
418+
SELECT JSON_VALUE(jsonb'{"d1": "foo"}','$.a2' RETURNING queryfuncs_test_domain DEFAULT'foo1'::queryfuncs_test_domainONEMPTY);
419419

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp