diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/sqljson_jsontable.out | 26 | ||||
| -rw-r--r-- | src/test/regress/expected/sqljson_queryfuncs.out | 16 | ||||
| -rw-r--r-- | src/test/regress/sql/sqljson_jsontable.sql | 7 | ||||
| -rw-r--r-- | src/test/regress/sql/sqljson_queryfuncs.sql | 5 |
4 files changed, 52 insertions, 2 deletions
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index 73039ea65eb..dfc7182ba9f 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -9,12 +9,12 @@ SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1 ERROR: invalid ON ERROR behavior LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1 ... ^ -DETAIL: Only EMPTY or ERROR is allowed in the top-level ON ERROR clause. +DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause. SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ERROR); ERROR: invalid ON ERROR behavior LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ER... ^ -DETAIL: Only EMPTY or ERROR is allowed in the top-level ON ERROR clause. +DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause. SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') EMPTY ON ERROR); js2 ----- @@ -1072,3 +1072,25 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on em ERROR: syntax error at or near "empty" LINE 1: ...sonb '1', '$' COLUMNS (a int exists empty object on empty)); ^ +-- Test ON ERROR / EMPTY value validity for the function and column types; +-- all fail +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ERROR); +ERROR: invalid ON ERROR behavior +LINE 1: ... * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ER... + ^ +DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause. +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on empty)); +ERROR: invalid ON EMPTY behavior for column "a" +LINE 1: ...T * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on em... + ^ +DETAIL: Only ERROR, NULL, or DEFAULT expression is allowed in ON EMPTY for scalar columns. +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on error)); +ERROR: invalid ON ERROR behavior for column "a" +LINE 1: ...N_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on er... + ^ +DETAIL: Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for formatted columns. +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on error)); +ERROR: invalid ON ERROR behavior for column "a" +LINE 1: ...M JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty obje... + ^ +DETAIL: Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for EXISTS columns. diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out index 9cb250a27a7..6ca17a3d426 100644 --- a/src/test/regress/expected/sqljson_queryfuncs.out +++ b/src/test/regress/expected/sqljson_queryfuncs.out @@ -1353,3 +1353,19 @@ SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz); 1 (1 row) +-- Test ON ERROR / EMPTY value validity for the function; all fail. +SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR); +ERROR: invalid ON ERROR behavior +LINE 1: SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR); + ^ +DETAIL: Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for JSON_EXISTS(). +SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR); +ERROR: invalid ON ERROR behavior +LINE 1: SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR); + ^ +DETAIL: Only ERROR, NULL, or DEFAULT expression is allowed in ON ERROR for JSON_VALUE(). +SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); +ERROR: invalid ON ERROR behavior +LINE 1: SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); + ^ +DETAIL: Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for JSON_QUERY(). diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index bda57981481..f1d99a8a736 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -521,3 +521,10 @@ DROP TABLE s; -- Prevent ON EMPTY specification on EXISTS columns SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on empty)); + +-- Test ON ERROR / EMPTY value validity for the function and column types; +-- all fail +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ERROR); +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on empty)); +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on error)); +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on error)); diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql index dc6380141b8..4586fdb8a4e 100644 --- a/src/test/regress/sql/sqljson_queryfuncs.sql +++ b/src/test/regress/sql/sqljson_queryfuncs.sql @@ -459,3 +459,8 @@ SELECT json_value('"aaa"', path RETURNING json) FROM jsonpaths; SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xy); SELECT JSON_QUERY(jsonb 'null', '$xy' PASSING 1 AS xyz); SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz); + +-- Test ON ERROR / EMPTY value validity for the function; all fail. +SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR); +SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); |