v6-0001-Fix-returning-of-json-b-domains-in-JSON_VALUE.patch

From e843bb377d0ff7132a1ccc43c07ba892d16628a9 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
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