From 358e7c7c83cb65f1eb310e2e78627dcc9d1b9bb3 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Mon, 29 Aug 2022 23:23:02 +0300 Subject: [PATCH v10 09/10] Remove support of DEFAULT ON EMPTY in SQL/JSON functions --- doc/src/sgml/func.sgml | 4 +- src/backend/executor/execExpr.c | 21 ------ src/backend/executor/execExprInterp.c | 27 ++++---- src/backend/jit/llvm/llvmjit_expr.c | 32 +++------ src/backend/optimizer/util/clauses.c | 13 ---- src/backend/parser/parse_expr.c | 33 ++------- src/include/executor/execExpr.h | 3 +- src/include/optimizer/clauses.h | 2 - src/test/regress/expected/jsonb_sqljson.out | 74 +++++++++------------ src/test/regress/sql/jsonb_sqljson.sql | 34 +++++----- 10 files changed, 82 insertions(+), 161 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 8dd63c04556..8e03a1831ab 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17976,7 +17976,7 @@ FROM context_item, path_expression PASSING { value AS varname } , ... RETURNING data_type - { ERROR | NULL | DEFAULT expression } ON EMPTY + { ERROR | NULL } ON EMPTY { ERROR | NULL | DEFAULT expression } ON ERROR ) @@ -18017,7 +18017,7 @@ FROM RETURNING data_type FORMAT JSON ENCODING UTF8 { WITHOUT | WITH { CONDITIONAL | UNCONDITIONAL } } ARRAY WRAPPER { KEEP | OMIT } QUOTES ON SCALAR STRING - { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON EMPTY + { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON EMPTY { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON ERROR ) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 68227ebee21..5ea7e6b421a 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -4166,9 +4166,7 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch, int skip_step_off; int execpath_step_off; int done_step_off; - int default_on_empty_step_off = -1; int default_on_error_step_off = -1; - int default_on_empty_jump_off = -1; /* JSON_TABLE preudo-function returns context item as a result */ if (jexpr->op == JSON_TABLE_OP) @@ -4235,16 +4233,6 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch, execpath_step_off = state->steps_len; ExprEvalPushStep(state, scratch); - if (jexpr->on_empty && jexpr->on_empty->default_expr) - { - default_on_empty_step_off = state->steps_len; - ExecInitExprRec((Expr *) jexpr->on_empty->default_expr, state, resv, resnull); - - scratch->opcode = EEOP_JUMP; - default_on_empty_jump_off = state->steps_len; - ExprEvalPushStep(state, scratch); - } - if (jexpr->on_error->default_expr) { default_on_error_step_off = state->steps_len; @@ -4261,15 +4249,6 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch, /* EEOP_JSONEXPR_PATH */ step = &state->steps[execpath_step_off]; step->d.jsonexpr.jump_done = done_step_off; - step->d.jsonexpr.jump_default_on_empty = - default_on_empty_step_off > 0 ? default_on_empty_step_off : done_step_off; step->d.jsonexpr.jump_default_on_error = default_on_error_step_off > 0 ? default_on_error_step_off : done_step_off; - - /* EEOP_JUMP */ - if (default_on_empty_jump_off > 0) - { - step = &state->steps[default_on_empty_jump_off]; - step->d.jump.jumpdone = done_step_off; - } } diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 22959ae569c..35f20d88232 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -1855,14 +1855,10 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_CASE(EEOP_JSONEXPR_PATH) { /* too complex for an inline implementation */ - int res = ExecEvalJsonExpr(state, op); - - if (res == 0) - EEO_JUMP(op->d.jsonexpr.jump_default_on_empty); - else if (res == -1) - EEO_JUMP(op->d.jsonexpr.jump_default_on_error); - else + if (ExecEvalJsonExpr(state, op)) EEO_JUMP(op->d.jsonexpr.jump_done); + else + EEO_JUMP(op->d.jsonexpr.jump_default_on_error); } EEO_CASE(EEOP_LAST) @@ -5403,11 +5399,10 @@ ExecEvalJsonExprSkip(ExprState *state, ExprEvalStep *op) * ---------------------------------------------------------------- * * Return value: - * 1 - Ok, jump to the end of JsonExpr - * 0 - empty result, need to execute DEFAULT ON EMPTY expression - * -1 - error occured, need to execute DEFAULT ON ERROR expression + * true - Ok, jump to the end of JsonExpr + * false - error occured, need to execute DEFAULT ON ERROR expression */ -int +bool ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op) { JsonExprState *jsestate = op->d.jsonexpr.jsestate; @@ -5439,14 +5434,16 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op) error = true; /* fallback to error behavior */ } - else if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT) - return 0; /* jump to DEFAULT ON EMPTY expression */ else + { + Assert(jexpr->on_empty->btype != JSON_BEHAVIOR_DEFAULT); /* not supported */ + /* Execute ON EMPTY behavior */ res = ExecEvalJsonBehavior(jexpr->on_empty, jexpr->returning->typid, jexpr->returning->typmod, op->resnull); + } } if (error) @@ -5454,7 +5451,7 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op) Assert(!throw_errors); if (jexpr->on_error->btype == JSON_BEHAVIOR_DEFAULT) - return -1; /* jump to DEFAULT ON ERROR expression */ + return false; /* jump to DEFAULT ON ERROR expression */ /* Execute ON ERROR behavior */ res = ExecEvalJsonBehavior(jexpr->on_error, @@ -5464,5 +5461,5 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op) } *op->resvalue = res; - return 1; /* jump to the end of expression */ + return true; /* jump to the end of expression */ } diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index ed53ad93443..69eb86aaf0a 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -2441,34 +2441,22 @@ llvm_compile_expr(ExprState *state) case EEOP_JSONEXPR_PATH: { LLVMValueRef v_ret; - LLVMValueRef v_switch; - int n_jumps = 0; v_ret = build_EvalXFunc(b, mod, "ExecEvalJsonExpr", v_state, op); - - if (op->d.jsonexpr.jump_default_on_empty != op->d.jsonexpr.jump_done) - n_jumps++; + v_ret = LLVMBuildZExt(b, v_ret, TypeStorageBool, ""); if (op->d.jsonexpr.jump_default_on_error != op->d.jsonexpr.jump_done) - n_jumps++; - - if (!n_jumps) - LLVMBuildBr(b, opblocks[op->d.jsonexpr.jump_done]); + LLVMBuildCondBr(b, + LLVMBuildICmp(b, + LLVMIntEQ, + v_ret, + l_sbool_const(0), + ""), + opblocks[op->d.jsonexpr.jump_default_on_error], + opblocks[op->d.jsonexpr.jump_done]); else - { - v_switch = LLVMBuildSwitch(b, v_ret, - opblocks[op->d.jsonexpr.jump_done], - n_jumps); - - if (op->d.jsonexpr.jump_default_on_empty != op->d.jsonexpr.jump_done) - LLVMAddCase(v_switch, l_int32_const(0), - opblocks[op->d.jsonexpr.jump_default_on_empty]); - - if (op->d.jsonexpr.jump_default_on_error != op->d.jsonexpr.jump_done) - LLVMAddCase(v_switch, l_int32_const(-1), - opblocks[op->d.jsonexpr.jump_default_on_error]); - } + LLVMBuildBr(b, opblocks[op->d.jsonexpr.jump_done]); break; } diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 6eb923892f4..b133dad10cf 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -5355,16 +5355,3 @@ pull_paramids_walker(Node *node, Bitmapset **context) return expression_tree_walker(node, pull_paramids_walker, (void *) context); } - -bool -expr_can_throw_errors(Node *expr) -{ - if (!expr) - return false; - - if (IsA(expr, Const)) - return false; - - /* TODO consider more cases */ - return true; -} diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index c02530a3671..f96334ce088 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4263,7 +4263,6 @@ static Node * coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, bool is_on_empty, Node *defexpr) { - Node *orig_defexpr = defexpr; int location; Oid exprtype; @@ -4276,6 +4275,12 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, if (location < 0) location = jsexpr->location; + if (is_on_empty) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("DEFAULT ON EMPTY expressions are not supported"), + parser_errposition(pstate, location))); + defexpr = coerce_to_target_type(pstate, defexpr, exprtype, @@ -4293,31 +4298,7 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, format_type_be(jsexpr->returning->typid)), parser_errposition(pstate, location))); - /* Don't allow unsafe DEFAUL ON EMPTY expressions, unless ERROR ON ERROR */ - if (!is_on_empty || - jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR) - return defexpr; - - if (!expr_can_throw_errors(defexpr)) - return defexpr; - - /* Try to simplify expression if there is non-empty coercion */ - if (defexpr != orig_defexpr && - !expr_can_throw_errors(orig_defexpr)) - { - defexpr = eval_const_expressions(NULL, defexpr); - - if (!expr_can_throw_errors(defexpr)) - return defexpr; - } - - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("unsafe DEFAULT ON EMPTY expressions are not supported"), - errhint("Use ERROR ON ERROR clause or try to simplify expression into constant-like form."), - parser_errposition(pstate, location))); - - return NULL; + return defexpr; } /* diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index b005bf440dd..d67419c66a9 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -698,7 +698,6 @@ typedef struct ExprEvalStep { struct JsonExprState *jsestate; int jump_done; - int jump_default_on_empty; /* ON EMPTY DEFAULT expression */ int jump_default_on_error; /* ON ERROR DEFAULT expression */ } jsonexpr; @@ -839,7 +838,7 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext, TupleTableSlot *slot); extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, ExprContext *econtext); -extern int ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op); +extern bool ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op); extern bool ExecEvalJsonExprSkip(ExprState *state, ExprEvalStep *op); extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup, diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index 4d61b88c161..6c5203dc448 100644 --- a/src/include/optimizer/clauses.h +++ b/src/include/optimizer/clauses.h @@ -55,6 +55,4 @@ extern Query *inline_set_returning_function(PlannerInfo *root, extern Bitmapset *pull_paramids(Expr *expr); -extern bool expr_can_throw_errors(Node *expr); - #endif /* CLAUSES_H */ diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index 0e07bbc858f..fcd921130f5 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -537,11 +537,9 @@ SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR); (1 row) SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR); - json_value ------------- - 2 -(1 row) - +ERROR: DEFAULT ON EMPTY expressions are not supported +LINE 1: SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY ... + ^ SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR); json_value ------------ @@ -576,7 +574,7 @@ SELECT jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING x AS x RETURNING int - DEFAULT -1 ON EMPTY + NULL ON EMPTY DEFAULT -2 ON ERROR ) y FROM @@ -585,7 +583,7 @@ FROM ---+---- 0 | -2 1 | 2 - 2 | -1 + 2 | (3 rows) SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a); @@ -634,31 +632,25 @@ SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 1 / x ON EMPTY DEFAULT 2 ON ERROR) FROM (VALUES (1::int), (0)) x(x); -ERROR: unsafe DEFAULT ON EMPTY expressions are not supported +ERROR: DEFAULT ON EMPTY expressions are not supported LINE 2: DEFAULT 1 / x ON EMPTY ^ -HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form. SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY); - json_value ------------- - -1 -(1 row) - +ERROR: DEFAULT ON EMPTY expressions are not supported +LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPT... + ^ SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY); - json_value ------------- - -1 -(1 row) - +ERROR: DEFAULT ON EMPTY expressions are not supported +LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EM... + ^ SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY); -ERROR: invalid input syntax for type integer: "err" +ERROR: DEFAULT ON EMPTY expressions are not supported LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON E... ^ SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY); -ERROR: unsafe DEFAULT ON EMPTY expressions are not supported +ERROR: DEFAULT ON EMPTY expressions are not supported LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ... ^ -HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form. -- JSON_QUERY SELECT JSON_QUERY(js, '$'), @@ -827,7 +819,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY); (1 row) -SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY); +SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY DEFAULT '"empty"' ON ERROR); json_query ------------ "empty" @@ -1055,7 +1047,7 @@ CREATE TABLE test_jsonb_constraints ( CONSTRAINT test_jsonb_constraint2 CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr)) CONSTRAINT test_jsonb_constraint3 - CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i) + CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int NULL ON EMPTY ERROR ON ERROR) > i) CONSTRAINT test_jsonb_constraint4 CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]') CONSTRAINT test_jsonb_constraint5 @@ -1073,7 +1065,7 @@ CREATE TABLE test_jsonb_constraints ( Check constraints: "test_jsonb_constraint1" CHECK (js IS JSON) "test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr)) - "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT ('12'::text || i)::integer ON EMPTY ERROR ON ERROR) > i) + "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer ERROR ON ERROR) > i) "test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb) "test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")) "test_jsonb_constraint6" CHECK (JSON_EXISTS(js::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2) @@ -1087,7 +1079,7 @@ ORDER BY 1; ((JSON_EXISTS((js)::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2)) ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))) ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb)) - ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT (('12'::text || i))::integer ON EMPTY ERROR ON ERROR) > i)) + ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer ERROR ON ERROR) > i)) ((js IS JSON)) (JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr)) (6 rows) @@ -1424,22 +1416,22 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR O ERROR: jsonpath member accessor can only be applied to an object SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; ERROR: no SQL/JSON item -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt; a --- 2 (1 row) -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt; a --- 2 (1 row) -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt; a --- - 1 + (1 row) -- JSON_TABLE: EXISTS PATH types @@ -1761,7 +1753,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1790,7 +1782,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1820,7 +1812,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1850,7 +1842,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1880,7 +1872,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1909,7 +1901,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1938,7 +1930,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1966,7 +1958,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -1994,7 +1986,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -2025,7 +2017,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -2195,7 +2187,7 @@ SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a'); 123 (1 row) -SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY); +SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' ERROR ON EMPTY DEFAULT 'foo' ON ERROR); json_value ------------ foo diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index 43a194ad09f..76d4c9cc14e 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -148,7 +148,7 @@ SELECT jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING x AS x RETURNING int - DEFAULT -1 ON EMPTY + NULL ON EMPTY DEFAULT -2 ON ERROR ) y FROM @@ -237,7 +237,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY); SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ARRAY ON EMPTY); SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY); SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY); -SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY); +SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY DEFAULT '"empty"' ON ERROR); SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR); SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR); @@ -309,7 +309,7 @@ CREATE TABLE test_jsonb_constraints ( CONSTRAINT test_jsonb_constraint2 CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr)) CONSTRAINT test_jsonb_constraint3 - CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i) + CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int NULL ON EMPTY ERROR ON ERROR) > i) CONSTRAINT test_jsonb_constraint4 CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]') CONSTRAINT test_jsonb_constraint5 @@ -515,9 +515,9 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt; -- JSON_TABLE: EXISTS PATH types SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a')); @@ -776,7 +776,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -791,7 +791,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -807,7 +807,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -823,7 +823,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -839,7 +839,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -855,7 +855,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -871,7 +871,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -887,7 +887,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -903,7 +903,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -919,7 +919,7 @@ from jtt.js,'strict $[*]' as p columns ( n for ordinality, - a int path 'lax $.a' default -1 on empty, + a int path 'lax $.a' error on empty default -1 on error, nested path 'strict $.b[*]' as pb columns ( b int path '$' ), nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) @@ -987,7 +987,7 @@ FROM JSON_TABLE( -- Extension: non-constant JSON path SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a'); SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a'); -SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY); +SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' ERROR ON EMPTY DEFAULT 'foo' ON ERROR); SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a'); SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); -- Should fail (invalid path) -- 2.17.1