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

Commitc0fc075

Browse files
committed
SQL/JSON: Fix issues with DEFAULT .. ON ERROR / EMPTY
SQL/JSON query functions allow specifying an expression to returnwhen either of ON ERROR or ON EMPTY condition occurs when evaluatingthe JSON path expression. The parser (transformJsonBehavior()) checksthat the specified expression is one of the supported expressions, butthere are two issues with how the check is done that are fixed in thiscommit:* No check for some expressions related to coercion, such as CoerceViaIO, that may appear in the transformed user-specified expressions that include cast(s)* An unsupported expression may be masked by a coercion-related expression, which must be flagged by checking the latter's argument expression recursivelyAuthor: Jian He <jian.universality@gmail.com>Author: Amit Langote <amitlangote09@gmail.com>Reported-by: Jian He <jian.universality@gmail.com>Discussion:https://postgr.es/m/CACJufxEqhqsfrg_p7EMyo5zak3d767iFDL8vz_4%3DZBHpOtrghw@mail.gmail.comDiscussion:https://postgr.es/m/CACJufxGOerH1QJknm1noh-Kz5FqU4p7QfeZSeVT2tN_4SLXYNg@mail.gmail.com
1 parentb4fad46 commitc0fc075

File tree

5 files changed

+167
-2
lines changed

5 files changed

+167
-2
lines changed

‎src/backend/parser/parse_expr.c

Lines changed: 35 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4616,6 +4616,40 @@ coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr)
46164616
(jsexpr->use_io_coercion!=jsexpr->use_json_coercion));
46174617
}
46184618

4619+
/*
4620+
* Recursively checks if the given expression, or its sub-node in some cases,
4621+
* is valid for using as an ON ERROR / ON EMPTY DEFAULT expression.
4622+
*/
4623+
staticbool
4624+
ValidJsonBehaviorDefaultExpr(Node*expr,void*context)
4625+
{
4626+
if (expr==NULL)
4627+
return false;
4628+
4629+
switch (nodeTag(expr))
4630+
{
4631+
/* Acceptable expression nodes */
4632+
caseT_Const:
4633+
caseT_FuncExpr:
4634+
caseT_OpExpr:
4635+
return true;
4636+
4637+
/* Acceptable iff arg of the following nodes is one of the above */
4638+
caseT_CoerceViaIO:
4639+
caseT_CoerceToDomain:
4640+
caseT_ArrayCoerceExpr:
4641+
caseT_ConvertRowtypeExpr:
4642+
caseT_RelabelType:
4643+
caseT_CollateExpr:
4644+
returnexpression_tree_walker(expr,ValidJsonBehaviorDefaultExpr,
4645+
context);
4646+
default:
4647+
break;
4648+
}
4649+
4650+
return false;
4651+
}
4652+
46194653
/*
46204654
* Transform a JSON BEHAVIOR clause.
46214655
*/
@@ -4636,8 +4670,7 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
46364670
if (btype==JSON_BEHAVIOR_DEFAULT)
46374671
{
46384672
expr=transformExprRecurse(pstate,behavior->expr);
4639-
if (!IsA(expr,Const)&& !IsA(expr,FuncExpr)&&
4640-
!IsA(expr,OpExpr))
4673+
if (!ValidJsonBehaviorDefaultExpr(expr,NULL))
46414674
ereport(ERROR,
46424675
(errcode(ERRCODE_DATATYPE_MISMATCH),
46434676
errmsg("can only specify a constant, non-aggregate function, or operator expression for DEFAULT"),

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

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -217,6 +217,38 @@ FROM json_table_test vals
217217
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | | "\"str\"" | ["\"str\""] | "str" | | |
218218
(14 rows)
219219

220+
-- Test using casts in DEFAULT .. ON ERROR expression
221+
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
222+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR));
223+
js1
224+
--------
225+
"foo1"
226+
(1 row)
227+
228+
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
229+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR));
230+
ERROR: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
231+
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
232+
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR));
233+
js1
234+
------
235+
foo1
236+
(1 row)
237+
238+
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
239+
COLUMNS (js1 jsonb_test_domain PATH '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
240+
js1
241+
------
242+
foo2
243+
(1 row)
244+
245+
SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
246+
COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON ERROR));
247+
js1
248+
-----
249+
{1}
250+
(1 row)
251+
220252
-- JSON_TABLE: Test backward parsing
221253
CREATE VIEW jsonb_table_view2 AS
222254
SELECT * FROM

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

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1222,6 +1222,63 @@ LINE 1: SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT (SELECT 1) ...
12221222
^
12231223
DROP TABLE test_jsonb_mutability;
12241224
DROP FUNCTION ret_setint;
1225+
CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
1226+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo'::jsonb_test_domain ON ERROR);
1227+
ERROR: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
1228+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
1229+
json_value
1230+
------------
1231+
foo1
1232+
(1 row)
1233+
1234+
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING jsonb_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR);
1235+
json_value
1236+
------------
1237+
"foo1"
1238+
(1 row)
1239+
1240+
SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' RETURNING jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR);
1241+
json_value
1242+
------------
1243+
foo1
1244+
(1 row)
1245+
1246+
-- Check the cases where a coercion-related expression is masking an
1247+
-- unsupported expressions
1248+
-- CoerceViaIO
1249+
SELECT JSON_QUERY('"a"', '$.a' RETURNING int DEFAULT (SELECT '"1"')::jsonb ON ERROR);
1250+
ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT
1251+
LINE 1: ...CT JSON_QUERY('"a"', '$.a' RETURNING int DEFAULT (SELECT '"...
1252+
^
1253+
-- CoerceToDomain
1254+
SELECT JSON_QUERY('"a"', '$.a' RETURNING jsonb_test_domain DEFAULT (select '"1"')::jsonb_test_domain ON ERROR);
1255+
ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT
1256+
LINE 1: ...('"a"', '$.a' RETURNING jsonb_test_domain DEFAULT (select '"...
1257+
^
1258+
-- RelabelType
1259+
SELECT JSON_QUERY('"a"', '$.a' RETURNING int DEFAULT (SELECT 1)::oid::int ON ERROR);
1260+
ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT
1261+
LINE 1: ...CT JSON_QUERY('"a"', '$.a' RETURNING int DEFAULT (SELECT 1)...
1262+
^
1263+
-- ArrayCoerceExpr
1264+
SELECT JSON_QUERY('"a"', '$.a' RETURNING int[] DEFAULT (SELECT '{1}')::oid[]::int[] ON ERROR);
1265+
ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT
1266+
LINE 1: ... JSON_QUERY('"a"', '$.a' RETURNING int[] DEFAULT (SELECT '{...
1267+
^
1268+
-- CollateExpr
1269+
SELECT JSON_QUERY('"a"', '$.a' RETURNING int[] DEFAULT (SELECT '{1}')::text COLLATE "C" ON ERROR);
1270+
ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT
1271+
LINE 1: ... JSON_QUERY('"a"', '$.a' RETURNING int[] DEFAULT (SELECT '{...
1272+
^
1273+
-- ConvertRowtypeExpr
1274+
CREATE TABLE someparent (a int);
1275+
CREATE TABLE somechild () INHERITS (someparent);
1276+
SELECT JSON_QUERY('"a"', '$.a' RETURNING someparent DEFAULT (SELECT '(1)')::somechild::someparent ON ERROR);
1277+
ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT
1278+
LINE 1: ..._QUERY('"a"', '$.a' RETURNING someparent DEFAULT (SELECT '(...
1279+
^
1280+
DROP DOMAIN jsonb_test_domain;
1281+
DROP TABLE someparent, somechild;
12251282
-- Extension: non-constant JSON path
12261283
SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
12271284
json_exists

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -116,6 +116,22 @@ FROM json_table_test vals
116116
) jt
117117
ON true;
118118

119+
-- Test using casts in DEFAULT .. ON ERROR expression
120+
SELECT*FROM JSON_TABLE(jsonb'{"d1": "H"}','$'
121+
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'"foo1"'::jsonb::textON ERROR));
122+
123+
SELECT*FROM JSON_TABLE(jsonb'{"d1": "H"}','$'
124+
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'foo'::jsonb_test_domainON ERROR));
125+
126+
SELECT*FROM JSON_TABLE(jsonb'{"d1": "H"}','$'
127+
COLUMNS (js1 jsonb_test_domainPATH'$.a2' DEFAULT'foo1'::jsonb_test_domainON ERROR));
128+
129+
SELECT*FROM JSON_TABLE(jsonb'{"d1": "foo"}','$'
130+
COLUMNS (js1 jsonb_test_domainPATH'$.d1' DEFAULT'foo2'::jsonb_test_domainON ERROR));
131+
132+
SELECT*FROM JSON_TABLE(jsonb'{"d1": "foo"}','$'
133+
COLUMNS (js1oid[]PATH'$.d2' DEFAULT'{1}'::int[]::oid[]ON ERROR));
134+
119135
-- JSON_TABLE: Test backward parsing
120136

121137
CREATEVIEWjsonb_table_view2AS

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

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -411,6 +411,33 @@ SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT (SELECT 1) ON ERROR) FROM test_
411411
DROPTABLE test_jsonb_mutability;
412412
DROPFUNCTION ret_setint;
413413

414+
CREATEDOMAINjsonb_test_domainAStextCHECK (value<>'foo');
415+
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING jsonb_test_domain DEFAULT'foo'::jsonb_test_domainON ERROR);
416+
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING jsonb_test_domain DEFAULT'foo1'::jsonb_test_domainON ERROR);
417+
SELECT JSON_VALUE(jsonb'{"d1": "H"}','$.a2' RETURNING jsonb_test_domain DEFAULT'"foo1"'::jsonb::textON ERROR);
418+
SELECT JSON_VALUE(jsonb'{"d1": "foo"}','$.a2' RETURNING jsonb_test_domain DEFAULT'foo1'::jsonb_test_domainON ERROR);
419+
420+
-- Check the cases where a coercion-related expression is masking an
421+
-- unsupported expressions
422+
423+
-- CoerceViaIO
424+
SELECT JSON_QUERY('"a"','$.a' RETURNINGint DEFAULT (SELECT'"1"')::jsonbON ERROR);
425+
-- CoerceToDomain
426+
SELECT JSON_QUERY('"a"','$.a' RETURNING jsonb_test_domain DEFAULT (select'"1"')::jsonb_test_domainON ERROR);
427+
-- RelabelType
428+
SELECT JSON_QUERY('"a"','$.a' RETURNINGint DEFAULT (SELECT1)::oid::intON ERROR);
429+
-- ArrayCoerceExpr
430+
SELECT JSON_QUERY('"a"','$.a' RETURNINGint[] DEFAULT (SELECT'{1}')::oid[]::int[]ON ERROR);
431+
-- CollateExpr
432+
SELECT JSON_QUERY('"a"','$.a' RETURNINGint[] DEFAULT (SELECT'{1}')::text COLLATE"C"ON ERROR);
433+
-- ConvertRowtypeExpr
434+
CREATETABLEsomeparent (aint);
435+
CREATETABLEsomechild () INHERITS (someparent);
436+
SELECT JSON_QUERY('"a"','$.a' RETURNING someparent DEFAULT (SELECT'(1)')::somechild::someparentON ERROR);
437+
438+
DROPDOMAIN jsonb_test_domain;
439+
DROPTABLE someparent, somechild;
440+
414441
-- Extension: non-constant JSON path
415442
SELECT JSON_EXISTS(jsonb'{"a": 123}','$'||'.'||'a');
416443
SELECT JSON_VALUE(jsonb'{"a": 123}','$'||'.'||'a');

0 commit comments

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