From e843bb377d0ff7132a1ccc43c07ba892d16628a9 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Mon, 15 Aug 2022 20:18:11 +0300 Subject: [PATCH v6 1/3] Fix returning of json[b] domains in JSON_VALUE() --- src/backend/executor/execExpr.c | 4 +- src/backend/executor/execExprInterp.c | 17 +- src/backend/optimizer/util/clauses.c | 2 +- src/backend/parser/parse_expr.c | 65 +++++-- src/include/executor/execExpr.h | 14 +- src/test/regress/expected/jsonb_sqljson.out | 181 +++++++++++++++++++- src/test/regress/sql/jsonb_sqljson.sql | 63 ++++++- 7 files changed, 318 insertions(+), 28 deletions(-) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index d0a57c7aaee..96196d50876 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -2645,13 +2645,15 @@ ExecInitExprRec(Expr *node, ExprState *state, Datum *caseval; bool *casenull; + jsestate->coercions = + palloc(sizeof(*jsestate->coercions)); jsestate->coercion_expr = palloc(sizeof(*jsestate->coercion_expr)); caseval = &jsestate->coercion_expr->value; casenull = &jsestate->coercion_expr->isnull; - for (cstate = &jsestate->coercions.null, + for (cstate = &jsestate->coercions->null, coercion = &jexpr->coercions->null; coercion <= &jexpr->coercions->composite; coercion++, cstate++) diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 636794ca6f1..353346d5528 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -5053,10 +5053,13 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, *resnull = false; /* coerce scalar item to the output type */ - if (jexpr->returning->typid == JSONOID || - jexpr->returning->typid == JSONBOID) + if (!jsestate->coercions) { - /* Use result coercion from json[b] to the output type */ + /* + * Use result coercion from json[b] to the output + * type when casting to json[b] types or their + * domains. + */ res = JsonbPGetDatum(JsonbValueToJsonb(jbv)); break; } @@ -5064,7 +5067,7 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, /* Use coercion from SQL/JSON item type to the output type */ res = ExecPrepareJsonItemCoercion(jbv, jsestate->jsexpr->returning, - &jsestate->coercions, + jsestate->coercions, &jcstate); if (jcstate->coercion && @@ -5161,7 +5164,7 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr, - struct JsonCoercionsState *coercions) + bool consider_conditional_coercions) { if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR) return false; @@ -5169,7 +5172,7 @@ ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr, if (jsexpr->op == JSON_EXISTS_OP && !jsexpr->result_coercion) return false; - if (!coercions) + if (consider_conditional_coercions) return true; return false; @@ -5218,7 +5221,7 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) var->evaluated = false; } - needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &jsestate->coercions); + needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, false); cxt.path = path; cxt.error = throwErrors ? NULL : &error; diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 533df86ff77..a1d868bb87b 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -901,7 +901,7 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context) { JsonExpr *jsexpr = (JsonExpr *) node; - if (ExecEvalJsonNeedsSubTransaction(jsexpr, NULL)) + if (ExecEvalJsonNeedsSubTransaction(jsexpr, true)) { context->max_hazard = PROPARALLEL_UNSAFE; return true; diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index fabb5f72076..31fb5555659 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4203,12 +4203,29 @@ coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning) return coercion; } +/* Is it a json/jsonb type or its domain? */ +static bool +isJsonType(Oid typid) +{ + Oid basetypid; + + if (typid == JSONOID || typid == JSONBOID) + return true; + + basetypid = getBaseType(typid); + + if (basetypid == JSONOID || basetypid == JSONBOID) + return true; + + return false; +} + /* * Transform a JSON output clause of JSON_VALUE and JSON_QUERY. */ static void transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, - JsonExpr *jsexpr) + JsonExpr *jsexpr, bool *returning_json) { Node *expr = jsexpr->formatted_expr; @@ -4225,13 +4242,20 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, { JsonReturning ret; - if (func->op == JSON_VALUE_OP && - jsexpr->returning->typid != JSONOID && - jsexpr->returning->typid != JSONBOID) + *returning_json = isJsonType(jsexpr->returning->typid); + + if (func->op == JSON_VALUE_OP && !*returning_json) { - /* Forced coercion via I/O for JSON_VALUE for non-JSON types */ + /* + * In JSON_VALUE result_coercion can only be used when + * returning JSON types and for NULL values (resulting + * item is JSON null or NULL behavior is used). + * So there is no need to find a cast from json[b] to + * output type when it is a non-JSON type, we only need to + * check domains for NOT NULL, what can be done using I/O + * coercion. + */ jsexpr->result_coercion = makeNode(JsonCoercion); - jsexpr->result_coercion->expr = NULL; jsexpr->result_coercion->via_io = true; return; } @@ -4251,8 +4275,11 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, } } else + { assignDefaultJsonReturningType(jsexpr->formatted_expr, jsexpr->format, jsexpr->returning); + *returning_json = true; + } } /* @@ -4321,10 +4348,11 @@ initJsonItemCoercion(ParseState *pstate, Oid typid, return coerceJsonExpr(pstate, expr, returning); } -static void -initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions, - const JsonReturning *returning, Oid contextItemTypeId) +static JsonItemCoercions * +initJsonItemCoercions(ParseState *pstate, const JsonReturning *returning, + Oid contextItemTypeId) { + JsonItemCoercions *coercions = makeNode(JsonItemCoercions); struct { JsonCoercion **coercion; @@ -4347,6 +4375,8 @@ initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions, for (p = coercionTypids; p->coercion; p++) *p->coercion = initJsonItemCoercion(pstate, p->typid, returning); + + return coercions; } /* @@ -4358,13 +4388,14 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) JsonExpr *jsexpr = transformJsonExprCommon(pstate, func); const char *func_name = NULL; Node *contextItemExpr = jsexpr->formatted_expr; + bool returning_json; switch (func->op) { case JSON_VALUE_OP: func_name = "JSON_VALUE"; - transformJsonFuncExprOutput(pstate, func, jsexpr); + transformJsonFuncExprOutput(pstate, func, jsexpr, &returning_json); jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT; jsexpr->returning->format->encoding = JS_ENC_DEFAULT; @@ -4377,16 +4408,22 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) coerceDefaultJsonExpr(pstate, jsexpr, jsexpr->on_error->default_expr); - jsexpr->coercions = makeNode(JsonItemCoercions); - initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning, - exprType(contextItemExpr)); + /* + * Don't initialize item coercions if returning one of JSON + * types or their domains. Coercion will be done using + * "result_coercion". + */ + if (!returning_json) + jsexpr->coercions = + initJsonItemCoercions(pstate, jsexpr->returning, + exprType(contextItemExpr)); break; case JSON_QUERY_OP: func_name = "JSON_QUERY"; - transformJsonFuncExprOutput(pstate, func, jsexpr); + transformJsonFuncExprOutput(pstate, func, jsexpr, &returning_json); jsexpr->on_empty->default_expr = coerceDefaultJsonExpr(pstate, jsexpr, diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index c8ef917ffe0..de9bbd2abcd 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -781,6 +781,15 @@ typedef struct JsonExprState void *cache; /* cache for json_populate_type() */ + /* + * This is used only in JSON_VALUE. + * + * States for coercion from SQL/JSON item types directly to the + * output type. + * + * "coercions == NULL" means output type is json[b] or its domain + * and we use "result_expr" for coercion. + */ struct JsonCoercionsState { struct JsonCoercionState @@ -797,8 +806,7 @@ typedef struct JsonExprState timestamp, timestamptz, composite; - } coercions; /* states for coercion from SQL/JSON item - * types directly to the output type */ + } *coercions; } JsonExprState; /* functions in execExpr.c */ @@ -867,7 +875,7 @@ extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item, struct JsonCoercionsState *coercions, struct JsonCoercionState **pjcstate); extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr, - struct JsonCoercionsState *); + bool consider_conditional_coercions); extern Datum ExecEvalExprPassingCaseValue(ExprState *estate, ExprContext *econtext, bool *isnull, Datum caseval_datum, diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index ef496110af3..ee5e597909c 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -349,14 +349,193 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; 03-01-2017 (1 row) --- Test NULL checks execution in domain types +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); + json_value +------------ + "1" +(1 row) + +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); + json_value +------------ + "1" +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null); + json_value +------------ + 1 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null); + json_value +------------ + 1 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null); + json_value +------------ + true +(1 row) + +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null); + json_value +------------ + true +(1 row) + +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR); + json_value +------------ + 2 +(1 row) + SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); ERROR: domain sqljsonb_int_not_null does not allow null values SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR); +ERROR: value for domain sqljsonb_json_not_null violates check constraint "sqljsonb_json_not_null_check" +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR); +ERROR: value for domain sqljsonb_jsonb_not_null violates check constraint "sqljsonb_jsonb_not_null_check" +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); json_value ------------ diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index fff25374808..d70104b70ef 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -82,12 +82,73 @@ SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234; SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; --- Test NULL checks execution in domain types +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); + +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null); + +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR); SELECT JSON_VALUE(jsonb '{}', '$'); -- 2.25.1