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

Commit55e56c8

Browse files
committed
SQL/JSON: Validate values in ON ERROR/EMPTY clauses
Currently, the grammar allows any supported values in the ON ERRORand ON EMPTY clauses for SQL/JSON functions, regardless of whetherthe values are appropriate for the function. This commit ensuresthat during parse analysis, the provided value is checked forvalidity for the given function and throws a syntax error if it isnot.While at it, this fixes some omissions in the documentation of theON ERROR/EMPTY clauses for JSON_TABLE().Reported-by: Jian He <jian.universality@gmail.com>Reviewed-by: Jian He <jian.universality@gmail.com>Discussion:https://postgr.es/m/CACJufxFgWGqpESSYzyJ6tSurr3vFYBSNEmCfkGyB_dMdptFnZQ%40mail.gmail.com
1 parente3c1393 commit55e56c8

File tree

7 files changed

+174
-14
lines changed

7 files changed

+174
-14
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -18939,7 +18939,7 @@ DETAIL: Missing "]" after array dimensions.
1893918939
JSON_TABLE (
1894018940
<replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional>
1894118941
COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> )
18942-
<optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> } <literal>ON ERROR</literal> </optional>
18942+
<optional> { <literal>ERROR</literal> | <literal>EMPTY</literal><optional>ARRAY</optional>} <literal>ON ERROR</literal> </optional>
1894318943
)
1894418944

1894518945
<phrase>
@@ -18951,8 +18951,8 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
1895118951
<optional> PATH <replaceable>path_expression</replaceable> </optional>
1895218952
<optional> { WITHOUT | WITH { CONDITIONAL | <optional>UNCONDITIONAL</optional> } } <optional> ARRAY </optional> WRAPPER </optional>
1895318953
<optional> { KEEP | OMIT } QUOTES <optional> ON SCALAR STRING </optional> </optional>
18954-
<optional> { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON EMPTY </optional>
18955-
<optional> { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON ERROR </optional>
18954+
<optional> { ERROR | NULL | EMPTY {<optional>ARRAY</optional> | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON EMPTY </optional>
18955+
<optional> { ERROR | NULL | EMPTY {<optional>ARRAY</optional> | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON ERROR </optional>
1895618956
| <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS <optional> PATH <replaceable>path_expression</replaceable> </optional>
1895718957
<optional> { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR </optional>
1895818958
| NESTED <optional> PATH </optional> <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )

‎src/backend/parser/parse_expr.c

Lines changed: 118 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -4300,14 +4300,124 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
43004300
}
43014301

43024302
/* OMIT QUOTES is meaningless when strings are wrapped. */
4303-
if (func->op==JSON_QUERY_OP&&
4304-
func->quotes==JS_QUOTES_OMIT&&
4305-
(func->wrapper==JSW_CONDITIONAL||
4306-
func->wrapper==JSW_UNCONDITIONAL))
4307-
ereport(ERROR,
4308-
errcode(ERRCODE_SYNTAX_ERROR),
4309-
errmsg("SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used"),
4310-
parser_errposition(pstate,func->location));
4303+
if (func->op==JSON_QUERY_OP)
4304+
{
4305+
if (func->quotes==JS_QUOTES_OMIT&&
4306+
(func->wrapper==JSW_CONDITIONAL||
4307+
func->wrapper==JSW_UNCONDITIONAL))
4308+
ereport(ERROR,
4309+
errcode(ERRCODE_SYNTAX_ERROR),
4310+
errmsg("SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used"),
4311+
parser_errposition(pstate,func->location));
4312+
if (func->on_empty!=NULL&&
4313+
func->on_empty->btype!=JSON_BEHAVIOR_ERROR&&
4314+
func->on_empty->btype!=JSON_BEHAVIOR_NULL&&
4315+
func->on_empty->btype!=JSON_BEHAVIOR_EMPTY&&
4316+
func->on_empty->btype!=JSON_BEHAVIOR_EMPTY_ARRAY&&
4317+
func->on_empty->btype!=JSON_BEHAVIOR_EMPTY_OBJECT&&
4318+
func->on_empty->btype!=JSON_BEHAVIOR_DEFAULT)
4319+
{
4320+
if (func->column_name==NULL)
4321+
ereport(ERROR,
4322+
errcode(ERRCODE_SYNTAX_ERROR),
4323+
errmsg("invalid ON EMPTY behavior"),
4324+
errdetail("Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON EMPTY for JSON_QUERY()."),
4325+
parser_errposition(pstate,func->on_empty->location));
4326+
else
4327+
ereport(ERROR,
4328+
errcode(ERRCODE_SYNTAX_ERROR),
4329+
errmsg("invalid ON EMPTY behavior for column \"%s\"",
4330+
func->column_name),
4331+
errdetail("Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON EMPTY for formatted columns."),
4332+
parser_errposition(pstate,func->on_empty->location));
4333+
}
4334+
if (func->on_error!=NULL&&
4335+
func->on_error->btype!=JSON_BEHAVIOR_ERROR&&
4336+
func->on_error->btype!=JSON_BEHAVIOR_NULL&&
4337+
func->on_error->btype!=JSON_BEHAVIOR_EMPTY&&
4338+
func->on_error->btype!=JSON_BEHAVIOR_EMPTY_ARRAY&&
4339+
func->on_error->btype!=JSON_BEHAVIOR_EMPTY_OBJECT&&
4340+
func->on_error->btype!=JSON_BEHAVIOR_DEFAULT)
4341+
{
4342+
if (func->column_name==NULL)
4343+
ereport(ERROR,
4344+
errcode(ERRCODE_SYNTAX_ERROR),
4345+
errmsg("invalid ON ERROR behavior"),
4346+
errdetail("Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for JSON_QUERY()."),
4347+
parser_errposition(pstate,func->on_error->location));
4348+
else
4349+
ereport(ERROR,
4350+
errcode(ERRCODE_SYNTAX_ERROR),
4351+
errmsg("invalid ON ERROR behavior for column \"%s\"",
4352+
func->column_name),
4353+
errdetail("Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for formatted columns."),
4354+
parser_errposition(pstate,func->on_error->location));
4355+
}
4356+
}
4357+
4358+
/* Check that ON ERROR/EMPTY behavior values are valid for the function. */
4359+
if (func->op==JSON_EXISTS_OP&&
4360+
func->on_error!=NULL&&
4361+
func->on_error->btype!=JSON_BEHAVIOR_ERROR&&
4362+
func->on_error->btype!=JSON_BEHAVIOR_TRUE&&
4363+
func->on_error->btype!=JSON_BEHAVIOR_FALSE&&
4364+
func->on_error->btype!=JSON_BEHAVIOR_UNKNOWN)
4365+
{
4366+
if (func->column_name==NULL)
4367+
ereport(ERROR,
4368+
errcode(ERRCODE_SYNTAX_ERROR),
4369+
errmsg("invalid ON ERROR behavior"),
4370+
errdetail("Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for JSON_EXISTS()."),
4371+
parser_errposition(pstate,func->on_error->location));
4372+
else
4373+
ereport(ERROR,
4374+
errcode(ERRCODE_SYNTAX_ERROR),
4375+
errmsg("invalid ON ERROR behavior for column \"%s\"",
4376+
func->column_name),
4377+
errdetail("Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for EXISTS columns."),
4378+
parser_errposition(pstate,func->on_error->location));
4379+
}
4380+
if (func->op==JSON_VALUE_OP)
4381+
{
4382+
if (func->on_empty!=NULL&&
4383+
func->on_empty->btype!=JSON_BEHAVIOR_ERROR&&
4384+
func->on_empty->btype!=JSON_BEHAVIOR_NULL&&
4385+
func->on_empty->btype!=JSON_BEHAVIOR_DEFAULT)
4386+
{
4387+
if (func->column_name==NULL)
4388+
ereport(ERROR,
4389+
errcode(ERRCODE_SYNTAX_ERROR),
4390+
errmsg("invalid ON EMPTY behavior"),
4391+
errdetail("Only ERROR, NULL, or DEFAULT expression is allowed in ON EMPTY for JSON_VALUE()."),
4392+
parser_errposition(pstate,func->on_empty->location));
4393+
else
4394+
ereport(ERROR,
4395+
errcode(ERRCODE_SYNTAX_ERROR),
4396+
errmsg("invalid ON EMPTY behavior for column \"%s\"",
4397+
func->column_name),
4398+
errdetail("Only ERROR, NULL, or DEFAULT expression is allowed in ON EMPTY for scalar columns."),
4399+
parser_errposition(pstate,func->on_empty->location));
4400+
}
4401+
if (func->on_error!=NULL&&
4402+
func->on_error->btype!=JSON_BEHAVIOR_ERROR&&
4403+
func->on_error->btype!=JSON_BEHAVIOR_NULL&&
4404+
func->on_error->btype!=JSON_BEHAVIOR_DEFAULT)
4405+
{
4406+
if (func->column_name==NULL)
4407+
ereport(ERROR,
4408+
errcode(ERRCODE_SYNTAX_ERROR),
4409+
errmsg("invalid ON ERROR behavior"),
4410+
errdetail("Only ERROR, NULL, or DEFAULT expression is allowed in ON ERROR for JSON_VALUE()."),
4411+
parser_errposition(pstate,func->on_error->location));
4412+
else
4413+
ereport(ERROR,
4414+
errcode(ERRCODE_SYNTAX_ERROR),
4415+
errmsg("invalid ON ERROR behavior for column \"%s\"",
4416+
func->column_name),
4417+
errdetail("Only ERROR, NULL, or DEFAULT expression is allowed in ON ERROR for scalar columns."),
4418+
parser_errposition(pstate,func->on_error->location));
4419+
}
4420+
}
43114421

43124422
jsexpr=makeNode(JsonExpr);
43134423
jsexpr->location=func->location;

‎src/backend/parser/parse_jsontable.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
9292
ereport(ERROR,
9393
errcode(ERRCODE_SYNTAX_ERROR),
9494
errmsg("invalid ON ERROR behavior"),
95-
errdetail("Only EMPTY or ERROR is allowed in the top-level ON ERROR clause."),
95+
errdetail("Only EMPTY[ ARRAY ]or ERROR is allowed in the top-level ON ERROR clause."),
9696
parser_errposition(pstate,jt->on_error->location));
9797

9898
cxt.pathNameId=0;

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

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,12 +9,12 @@ SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1
99
ERROR: invalid ON ERROR behavior
1010
LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1 ...
1111
^
12-
DETAIL: Only EMPTY or ERROR is allowed in the top-level ON ERROR clause.
12+
DETAIL: Only EMPTY[ ARRAY ]or ERROR is allowed in the top-level ON ERROR clause.
1313
SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ERROR);
1414
ERROR: invalid ON ERROR behavior
1515
LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ER...
1616
^
17-
DETAIL: Only EMPTY or ERROR is allowed in the top-level ON ERROR clause.
17+
DETAIL: Only EMPTY[ ARRAY ]or ERROR is allowed in the top-level ON ERROR clause.
1818
SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') EMPTY ON ERROR);
1919
js2
2020
-----
@@ -1072,3 +1072,25 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on em
10721072
ERROR: syntax error at or near "empty"
10731073
LINE 1: ...sonb '1', '$' COLUMNS (a int exists empty object on empty));
10741074
^
1075+
-- Test ON ERROR / EMPTY value validity for the function and column types;
1076+
-- all fail
1077+
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ERROR);
1078+
ERROR: invalid ON ERROR behavior
1079+
LINE 1: ... * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ER...
1080+
^
1081+
DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause.
1082+
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on empty));
1083+
ERROR: invalid ON EMPTY behavior for column "a"
1084+
LINE 1: ...T * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on em...
1085+
^
1086+
DETAIL: Only ERROR, NULL, or DEFAULT expression is allowed in ON EMPTY for scalar columns.
1087+
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on error));
1088+
ERROR: invalid ON ERROR behavior for column "a"
1089+
LINE 1: ...N_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on er...
1090+
^
1091+
DETAIL: Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for formatted columns.
1092+
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on error));
1093+
ERROR: invalid ON ERROR behavior for column "a"
1094+
LINE 1: ...M JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty obje...
1095+
^
1096+
DETAIL: Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for EXISTS columns.

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1353,3 +1353,19 @@ SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz);
13531353
1
13541354
(1 row)
13551355

1356+
-- Test ON ERROR / EMPTY value validity for the function; all fail.
1357+
SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
1358+
ERROR: invalid ON ERROR behavior
1359+
LINE 1: SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
1360+
^
1361+
DETAIL: Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for JSON_EXISTS().
1362+
SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR);
1363+
ERROR: invalid ON ERROR behavior
1364+
LINE 1: SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR);
1365+
^
1366+
DETAIL: Only ERROR, NULL, or DEFAULT expression is allowed in ON ERROR for JSON_VALUE().
1367+
SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);
1368+
ERROR: invalid ON ERROR behavior
1369+
LINE 1: SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);
1370+
^
1371+
DETAIL: Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for JSON_QUERY().

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -521,3 +521,10 @@ DROP TABLE s;
521521

522522
-- Prevent ON EMPTY specification on EXISTS columns
523523
SELECT*FROM JSON_TABLE(jsonb'1','$' COLUMNS (aint exists empty objecton empty));
524+
525+
-- Test ON ERROR / EMPTY value validity for the function and column types;
526+
-- all fail
527+
SELECT*FROM JSON_TABLE(jsonb'1','$' COLUMNS (aint)NULLON ERROR);
528+
SELECT*FROM JSON_TABLE(jsonb'1','$' COLUMNS (aint trueon empty));
529+
SELECT*FROM JSON_TABLE(jsonb'1','$' COLUMNS (aint omit quotes trueon error));
530+
SELECT*FROM JSON_TABLE(jsonb'1','$' COLUMNS (aint exists empty objecton error));

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -459,3 +459,8 @@ SELECT json_value('"aaa"', path RETURNING json) FROM jsonpaths;
459459
SELECT JSON_QUERY(jsonb'null','$xyz' PASSING1AS xy);
460460
SELECT JSON_QUERY(jsonb'null','$xy' PASSING1AS xyz);
461461
SELECT JSON_QUERY(jsonb'null','$xyz' PASSING1AS xyz);
462+
463+
-- Test ON ERROR / EMPTY value validity for the function; all fail.
464+
SELECT JSON_EXISTS(jsonb'1','$' DEFAULT1ON ERROR);
465+
SELECT JSON_VALUE(jsonb'1','$' EMPTYON ERROR);
466+
SELECT JSON_QUERY(jsonb'1','$' TRUEON ERROR);

0 commit comments

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