v3-0001-SQL-JSON-Always-coerce-JsonExpr-result-at-runtime.patch

From 4660ecb1869884e7024b6fc0fbb10f5084f8bf3f Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Thu, 27 Jun 2024 16:07:39 +0900
Subject: [PATCH v3 1/2] SQL/JSON: Always coerce JsonExpr result at runtime

Instead of looking up casts at parse time for converting the result
of JsonPath* query functions to the specified or the default
RETURNING type, always perform the conversion at runtime using either
the target type's input function or the function
json_populate_type().  The main motivation for this change is to
avoid ending up in some cases with a cast expression that doesn't
support soft handling of errors.

JsonExpr.coercion_expr which would store the cast expression is no
longer necessary, so remove.
---
 src/backend/executor/execExpr.c               |  42 ++----
 src/backend/executor/execExprInterp.c         |  31 ++--
 src/backend/nodes/nodeFuncs.c                 |  13 +-
 src/backend/parser/parse_expr.c               | 141 ++++--------------
 src/backend/utils/adt/jsonfuncs.c             |  22 ++-
 src/include/executor/execExpr.h               |   1 +
 src/include/nodes/primnodes.h                 |   6 -
 src/include/utils/jsonfuncs.h                 |   1 +
 .../regress/expected/sqljson_jsontable.out    |  52 +++----
 .../regress/expected/sqljson_queryfuncs.out   |  72 ++++++---
 src/test/regress/sql/sqljson_jsontable.sql    |   2 +-
 src/test/regress/sql/sqljson_queryfuncs.sql   |  25 +++-
 12 files changed, 170 insertions(+), 238 deletions(-)

diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 2bf86d06ef..a8be57d21b 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -92,7 +92,7 @@ static void ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 							 Datum *resv, bool *resnull,
 							 ExprEvalStep *scratch);
 static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
-								 ErrorSaveContext *escontext,
+								 ErrorSaveContext *escontext, bool omit_quotes,
 								 Datum *resv, bool *resnull);
 
 
@@ -4318,7 +4318,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 	 * constraints must be checked.  jsexpr->coercion_expr containing a
 	 * CoerceToDomain node must have been set in that case.
 	 */
-	if (jsexpr->coercion_expr)
+	if (jsexpr->use_json_coercion)
 	{
 		scratch->opcode = EEOP_JUMP;
 		scratch->d.jump.jumpdone = state->steps_len + 1;
@@ -4337,33 +4337,12 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 	 * NULL returned on NULL input as described above.
 	 */
 	jsestate->jump_eval_coercion = -1;
-	if (jsexpr->coercion_expr)
-	{
-		Datum	   *save_innermost_caseval;
-		bool	   *save_innermost_casenull;
-		ErrorSaveContext *save_escontext;
-
-		jsestate->jump_eval_coercion = state->steps_len;
-
-		save_innermost_caseval = state->innermost_caseval;
-		save_innermost_casenull = state->innermost_casenull;
-		save_escontext = state->escontext;
-
-		state->innermost_caseval = resv;
-		state->innermost_casenull = resnull;
-		state->escontext = escontext;
-
-		ExecInitExprRec((Expr *) jsexpr->coercion_expr, state, resv, resnull);
-
-		state->innermost_caseval = save_innermost_caseval;
-		state->innermost_casenull = save_innermost_casenull;
-		state->escontext = save_escontext;
-	}
-	else if (jsexpr->use_json_coercion)
+	if (jsexpr->use_json_coercion)
 	{
 		jsestate->jump_eval_coercion = state->steps_len;
 
-		ExecInitJsonCoercion(state, jsexpr->returning, escontext, resv, resnull);
+		ExecInitJsonCoercion(state, jsexpr->returning, escontext,
+							 jsexpr->omit_quotes, resv, resnull);
 	}
 	else if (jsexpr->use_io_coercion)
 	{
@@ -4435,8 +4414,8 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 
 		/* Step to coerce the ON ERROR expression if needed */
 		if (jsexpr->on_error->coerce)
-			ExecInitJsonCoercion(state, jsexpr->returning, escontext, resv,
-								 resnull);
+			ExecInitJsonCoercion(state, jsexpr->returning, escontext,
+								 jsexpr->omit_quotes, resv, resnull);
 
 		/* JUMP to end to skip the ON EMPTY steps added below. */
 		jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
@@ -4468,8 +4447,8 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
 
 		/* Step to coerce the ON EMPTY expression if needed */
 		if (jsexpr->on_empty->coerce)
-			ExecInitJsonCoercion(state, jsexpr->returning, escontext, resv,
-								 resnull);
+			ExecInitJsonCoercion(state, jsexpr->returning, escontext,
+								 jsexpr->omit_quotes, resv, resnull);
 	}
 
 	foreach(lc, jumps_to_end)
@@ -4488,7 +4467,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
  */
 static void
 ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
-					 ErrorSaveContext *escontext,
+					 ErrorSaveContext *escontext, bool omit_quotes,
 					 Datum *resv, bool *resnull)
 {
 	ExprEvalStep scratch = {0};
@@ -4501,5 +4480,6 @@ ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
 	scratch.d.jsonexpr_coercion.targettypmod = returning->typmod;
 	scratch.d.jsonexpr_coercion.json_populate_type_cache = NULL;
 	scratch.d.jsonexpr_coercion.escontext = escontext;
+	scratch.d.jsonexpr_coercion.omit_quotes = omit_quotes;
 	ExprEvalPushStep(state, &scratch);
 }
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 852186312c..3a13f90d48 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4303,8 +4303,14 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 
 				if (!error)
 				{
-					*op->resvalue = BoolGetDatum(exists);
 					*op->resnull = false;
+					if (jsexpr->use_json_coercion)
+						*op->resvalue = DirectFunctionCall1(jsonb_in,
+															BoolGetDatum(exists) ?
+															CStringGetDatum("true") :
+															CStringGetDatum("false"));
+					else
+						*op->resvalue = BoolGetDatum(exists);
 				}
 			}
 			break;
@@ -4317,21 +4323,9 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 
 			*op->resnull = (DatumGetPointer(*op->resvalue) == NULL);
 
-			/* Handle OMIT QUOTES. */
-			if (!*op->resnull && jsexpr->omit_quotes)
-			{
+			if (!*op->resnull && jsexpr->use_io_coercion)
 				val_string = JsonbUnquote(DatumGetJsonbP(*op->resvalue));
 
-				/*
-				 * Pass the string as a text value to the cast expression if
-				 * one present.  If not, use the input function call below to
-				 * do the coercion.
-				 */
-				if (jump_eval_coercion >= 0)
-					*op->resvalue =
-						DirectFunctionCall1(textin,
-											PointerGetDatum(val_string));
-			}
 			break;
 
 		case JSON_VALUE_OP:
@@ -4355,6 +4349,11 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 						val_string = DatumGetCString(DirectFunctionCall1(jsonb_out,
 																		 JsonbPGetDatum(JsonbValueToJsonb(jbv))));
 					}
+					else if (jsexpr->use_json_coercion)
+					{
+						*op->resvalue = JsonbPGetDatum(JsonbValueToJsonb(jbv));
+						*op->resnull = false;
+					}
 					else
 					{
 						val_string = ExecGetJsonValueItemString(jbv, op->resnull);
@@ -4545,7 +4544,9 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
 									   op->d.jsonexpr_coercion.targettypmod,
 									   &op->d.jsonexpr_coercion.json_populate_type_cache,
 									   econtext->ecxt_per_query_memory,
-									   op->resnull, (Node *) escontext);
+									   op->resnull,
+									   op->d.jsonexpr_coercion.omit_quotes,
+									   (Node *) escontext);
 }
 
 /*
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 89ee4b61f2..d2e2af4f81 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1006,10 +1006,7 @@ exprCollation(const Node *expr)
 			{
 				const JsonExpr *jsexpr = (JsonExpr *) expr;
 
-				if (jsexpr->coercion_expr)
-					coll = exprCollation(jsexpr->coercion_expr);
-				else
-					coll = jsexpr->collation;
+				coll = jsexpr->collation;
 			}
 			break;
 		case T_JsonBehavior:
@@ -1265,10 +1262,7 @@ exprSetCollation(Node *expr, Oid collation)
 			{
 				JsonExpr   *jexpr = (JsonExpr *) expr;
 
-				if (jexpr->coercion_expr)
-					exprSetCollation((Node *) jexpr->coercion_expr, collation);
-				else
-					jexpr->collation = collation;
+				jexpr->collation = collation;
 			}
 			break;
 		case T_JsonBehavior:
@@ -2368,8 +2362,6 @@ expression_tree_walker_impl(Node *node,
 					return true;
 				if (WALK(jexpr->path_spec))
 					return true;
-				if (WALK(jexpr->coercion_expr))
-					return true;
 				if (WALK(jexpr->passing_values))
 					return true;
 				/* we assume walker doesn't care about passing_names */
@@ -3411,7 +3403,6 @@ expression_tree_mutator_impl(Node *node,
 				FLATCOPY(newnode, jexpr, JsonExpr);
 				MUTATE(newnode->formatted_expr, jexpr->formatted_expr, Node *);
 				MUTATE(newnode->path_spec, jexpr->path_spec, Node *);
-				MUTATE(newnode->coercion_expr, jexpr->coercion_expr, Node *);
 				MUTATE(newnode->passing_values, jexpr->passing_values, List *);
 				/* assume mutator does not care about passing_names */
 				MUTATE(newnode->on_empty, jexpr->on_empty, JsonBehavior *);
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 00cd7358eb..97a92f2b0d 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4360,39 +4360,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 
 			/* JSON_TABLE() COLUMNS can specify a non-boolean type. */
 			if (jsexpr->returning->typid != BOOLOID)
-			{
-				Node	   *coercion_expr;
-				CaseTestExpr *placeholder = makeNode(CaseTestExpr);
-				int			location = exprLocation((Node *) jsexpr);
-
-				/*
-				 * We abuse CaseTestExpr here as placeholder to pass the
-				 * result of evaluating JSON_EXISTS to the coercion
-				 * expression.
-				 */
-				placeholder->typeId = BOOLOID;
-				placeholder->typeMod = -1;
-				placeholder->collation = InvalidOid;
-
-				coercion_expr =
-					coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID,
-										  jsexpr->returning->typid,
-										  jsexpr->returning->typmod,
-										  COERCION_EXPLICIT,
-										  COERCE_IMPLICIT_CAST,
-										  location);
-
-				if (coercion_expr == NULL)
-					ereport(ERROR,
-							(errcode(ERRCODE_CANNOT_COERCE),
-							 errmsg("cannot cast type %s to %s",
-									format_type_be(BOOLOID),
-									format_type_be(jsexpr->returning->typid)),
-							 parser_coercion_errposition(pstate, location, (Node *) jsexpr)));
-
-				if (coercion_expr != (Node *) placeholder)
-					jsexpr->coercion_expr = coercion_expr;
-			}
+				jsexpr->use_json_coercion = true;
 
 			jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
 													 JSON_BEHAVIOR_FALSE,
@@ -4517,12 +4485,8 @@ static void
 coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr)
 {
 	JsonReturning *returning = jsexpr->returning;
-	Node	   *context_item = jsexpr->formatted_expr;
-	int			default_typmod;
-	Oid			default_typid;
 	bool		omit_quotes =
 		jsexpr->op == JSON_QUERY_OP && jsexpr->omit_quotes;
-	Node	   *coercion_expr = NULL;
 
 	Assert(returning);
 
@@ -4533,95 +4497,29 @@ coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr)
 	if (jsexpr->op == JSON_VALUE_OP)
 	{
 		/*
-		 * Use cast expressions for types with typmod and domain types.
+		 * Use json_populate_type() when coercing to domain types with
+		 * constraints, coercion using IO won't check them.
 		 */
-		if (returning->typmod == -1 &&
-			get_typtype(returning->typid) != TYPTYPE_DOMAIN)
+		if (get_typtype(returning->typid) != TYPTYPE_DOMAIN ||
+			!DomainHasConstraints(returning->typid))
 		{
 			jsexpr->use_io_coercion = true;
 			return;
 		}
-	}
-	else if (jsexpr->op == JSON_QUERY_OP)
-	{
-		/*
-		 * Cast functions from jsonb to the following types (jsonb_bool() et
-		 * al) don't handle errors softly, so coerce either by calling
-		 * json_populate_type() or the type's input function so that any
-		 * errors are handled appropriately. The latter only if OMIT QUOTES is
-		 * true.
-		 */
-		switch (returning->typid)
+		else
 		{
-			case BOOLOID:
-			case NUMERICOID:
-			case INT2OID:
-			case INT4OID:
-			case INT8OID:
-			case FLOAT4OID:
-			case FLOAT8OID:
-				if (jsexpr->omit_quotes)
-					jsexpr->use_io_coercion = true;
-				else
-					jsexpr->use_json_coercion = true;
-				return;
-			default:
-				break;
+			jsexpr->use_json_coercion = true;
+			return;
 		}
 	}
-
-	/* Look up a cast expression. */
-
-	/*
-	 * For JSON_VALUE() and for JSON_QUERY() when OMIT QUOTES is true,
-	 * ExecEvalJsonExprPath() will convert a quote-stripped source value to
-	 * its text representation, so use TEXTOID as the source type.
-	 */
-	if (omit_quotes || jsexpr->op == JSON_VALUE_OP)
-	{
-		default_typid = TEXTOID;
-		default_typmod = -1;
-	}
-	else
-	{
-		default_typid = exprType(context_item);
-		default_typmod = exprTypmod(context_item);
-	}
-
-	if (returning->typid != default_typid ||
-		returning->typmod != default_typmod)
-	{
-		/*
-		 * We abuse CaseTestExpr here as placeholder to pass the result of
-		 * jsonpath evaluation as input to the coercion expression.
-		 */
-		CaseTestExpr *placeholder = makeNode(CaseTestExpr);
-
-		placeholder->typeId = default_typid;
-		placeholder->typeMod = default_typmod;
-
-		coercion_expr = coerceJsonFuncExpr(pstate, (Node *) placeholder,
-										   returning, false);
-		if (coercion_expr == (Node *) placeholder)
-			coercion_expr = NULL;
-	}
-
-	jsexpr->coercion_expr = coercion_expr;
-
-	if (coercion_expr == NULL)
+	else if (jsexpr->op == JSON_QUERY_OP)
 	{
-		/*
-		 * Either no cast was found or coercion is unnecessary but still must
-		 * convert the string value to the output type.
-		 */
-		if (omit_quotes || jsexpr->op == JSON_VALUE_OP)
+		if (omit_quotes)
 			jsexpr->use_io_coercion = true;
 		else
 			jsexpr->use_json_coercion = true;
+		return;
 	}
-
-	Assert(jsexpr->coercion_expr != NULL ||
-		   (jsexpr->use_io_coercion != jsexpr->use_json_coercion));
 }
 
 /*
@@ -4716,11 +4614,24 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
 			 btype == default_behavior))
 			coerce_at_runtime = true;
 		else
+		{
+			int32	baseTypmod = returning->typmod;
+
+			if (get_typtype(returning->typid) == TYPTYPE_DOMAIN)
+				(void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+
+			if (baseTypmod > 0)
+				expr = coerce_to_specific_type(pstate, expr, TEXTOID,
+											   "JSON_FUNCTION()");
 			coerced_expr =
 				coerce_to_target_type(pstate, expr, exprType(expr),
-									  returning->typid, returning->typmod,
-									  COERCION_EXPLICIT, COERCE_EXPLICIT_CAST,
+									  returning->typid, baseTypmod,
+									  baseTypmod > 0 ? COERCION_IMPLICIT :
+									  COERCION_EXPLICIT,
+									  baseTypmod > 0 ? COERCE_IMPLICIT_CAST :
+									  COERCE_EXPLICIT_CAST,
 									  exprLocation((Node *) behavior));
+		}
 
 		if (coerced_expr == NULL)
 			ereport(ERROR,
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index ab5aa0ccb8..b26a1cbfcb 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -3338,7 +3338,7 @@ Datum
 json_populate_type(Datum json_val, Oid json_type,
 				   Oid typid, int32 typmod,
 				   void **cache, MemoryContext mcxt,
-				   bool *isnull,
+				   bool *isnull, bool omit_quotes,
 				   Node *escontext)
 {
 	JsValue		jsv = {0};
@@ -3368,10 +3368,22 @@ json_populate_type(Datum json_val, Oid json_type,
 
 		jsv.val.jsonb = &jbv;
 
-		/* fill binary jsonb value pointing to jb */
-		jbv.type = jbvBinary;
-		jbv.val.binary.data = &jsonb->root;
-		jbv.val.binary.len = VARSIZE(jsonb) - VARHDRSZ;
+		if (omit_quotes)
+		{
+			char	   *str = JsonbUnquote(DatumGetJsonbP(json_val));
+
+			/* fill the quote-stripped string */
+			jbv.type = jbvString;
+			jbv.val.string.len = strlen(str);
+			jbv.val.string.val = str;
+		}
+		else
+		{
+			/* fill binary jsonb value pointing to jb */
+			jbv.type = jbvBinary;
+			jbv.val.binary.data = &jsonb->root;
+			jbv.val.binary.len = VARSIZE(jsonb) - VARHDRSZ;
+		}
 	}
 
 	if (*cache == NULL)
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 64698202a5..55337d4916 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -707,6 +707,7 @@ typedef struct ExprEvalStep
 		{
 			Oid			targettype;
 			int32		targettypmod;
+			bool		omit_quotes;
 			void	   *json_populate_type_cache;
 			ErrorSaveContext *escontext;
 		}			jsonexpr_coercion;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4830efc573..dacc75f2af 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1838,13 +1838,7 @@ typedef struct JsonExpr
 	/*
 	 * Information about converting the result of jsonpath functions
 	 * JsonPathQuery() and JsonPathValue() to the RETURNING type.
-	 *
-	 * coercion_expr is a cast expression if the parser can find it for the
-	 * source and the target type.  If not, either use_io_coercion or
-	 * use_json_coercion is set to determine the coercion method to use at
-	 * runtime; see coerceJsonExprOutput() and ExecInitJsonExpr().
 	 */
-	Node	   *coercion_expr;
 	bool		use_io_coercion;
 	bool		use_json_coercion;
 
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 190e13284b..93384d900a 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -93,6 +93,7 @@ extern Datum json_populate_type(Datum json_val, Oid json_type,
 								Oid typid, int32 typmod,
 								void **cache, MemoryContext mcxt,
 								bool *isnull,
+								bool omit_quotes,
 								Node *escontext);
 
 #endif
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index cee90cead1..cf43442891 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -103,14 +103,14 @@ FROM json_table_test vals
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  1 |   1 | 1       | 1       | t    |       1 | 1       | 1            | 1
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  2 |     | 1.23    | 1.23    |      |    1.23 | 1.23    | 1.23         | 1.23
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  3 |   2 | 2       | 2       |      |       2 | 2       | "2"          | "2"
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  4 |     | aaaaaaa | aaaa    |      |         | aaaaaaa | "aaaaaaa"    | "aaaaaaa"
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  4 |     | aaaaaaa |         |      |         | aaaaaaa | "aaaaaaa"    | "aaaaaaa"
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  5 |     | foo     | foo     |      |         |         | "foo"        | "foo"
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  6 |     |         |         |      |         |         | null         | null
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  7 |     | f       | f       | f    |         | f       | false        | false
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  8 |     | t       | t       | t    |         | t       | true         | true
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  7 |     | f       | f       | f    |         | false   | false        | false
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  8 |     | t       | t       | t    |         | true    | true         | true
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  9 |     |         |         |      |         |         | {"aaa": 123} | {"aaa": 123}
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 |     | [1,2]   | [1,2    |      |         | [1,2]   | "[1,2]"      | "[1,2]"
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 |     | "str"   | "str    |      |         | "str"   | "\"str\""    | "\"str\""
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 |     | [1,2]   |         |      |         | [1,2]   | "[1,2]"      | "[1,2]"
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 |     | "str"   |         |      |         | "str"   | "\"str\""    | "\"str\""
 (14 rows)
 
 -- "formatted" columns
@@ -137,14 +137,14 @@ FROM json_table_test vals
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  1 | 1            | 1    | 1    | 1            | 1
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  2 | 1.23         | 1.23 | 1.23 | 1.23         | 1.23
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  3 | "2"          | "2"  | "2"  | "2"          | 2
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  4 | "aaaaaaa"    | "aaa | "aaa | "aaaaaaa"    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  5 | "foo"        | "foo | "foo | "foo"        | 
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  4 | "aaaaaaa"    |      |      | "aaaaaaa"    | 
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  5 | "foo"        |      |      | "foo"        | 
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  6 | null         | null | null | null         | null
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  7 | false        | fals | fals | false        | false
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  7 | false        |      |      | false        | false
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  8 | true         | true | true | true         | true
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  9 | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123}
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | "[1,2]"      | "[1, | "[1, | "[1,2]"      | [1, 2]
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | "\"str\""    | "\"s | "\"s | "\"str\""    | "str"
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  9 | {"aaa": 123} |      |      | {"aaa": 123} | {"aaa": 123}
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | "[1,2]"      |      |      | "[1,2]"      | [1, 2]
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | "\"str\""    |      |      | "\"str\""    | "str"
 (14 rows)
 
 -- EXISTS columns
@@ -175,7 +175,7 @@ FROM json_table_test vals
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  6 | f       |       0 |         | false
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  7 | f       |       0 |         | false
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  8 | f       |       0 |         | false
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  9 | t       |       1 |       1 | true
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  9 | t       |       0 |         | true
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | f       |       0 |         | false
  [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | f       |       0 |         | false
 (14 rows)
@@ -555,31 +555,21 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
 (1 row)
 
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to smallint
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXI...
-                                                             ^
+ERROR:  cannot cast behavior expression of type boolean to smallint
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to bigint
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXI...
-                                                             ^
+ERROR:  cannot cast behavior expression of type boolean to bigint
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to real
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 E...
-                                                             ^
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
-  a  
------
- fal
+ERROR:  cannot cast behavior expression of type boolean to real
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a'));
+   a   
+-------
+ false
 (1 row)
 
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to json
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXI...
-                                                             ^
+ERROR:  cannot cast behavior expression of type boolean to json
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to jsonb
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX...
-                                                             ^
+ERROR:  cannot cast behavior expression of type boolean to jsonb
 -- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
   item   
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 9cb250a27a..ea136eefa7 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -234,10 +234,18 @@ SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5));
  aaa  
 (1 row)
 
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2) ERROR ON ERROR);
+ERROR:  value too long for type character(2)
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2));
  json_value 
 ------------
- aa
+ 
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR);
+ json_value 
+------------
+ aaa
 (1 row)
 
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json);
@@ -636,30 +644,28 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERR
 (1 row)
 
 -- Behavior when a RETURNING type has typmod != -1
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2));
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR);
+ERROR:  value too long for type character(3)
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3));
  json_query 
 ------------
- "a
+ 
 (1 row)
 
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2) OMIT QUOTES);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) OMIT QUOTES ERROR ON ERROR);
  json_query 
 ------------
- aa
+ aaa
 (1 row)
 
-SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bbb' ON EMPTY);
+SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bb' ON EMPTY);
  json_query 
 ------------
  bb
 (1 row)
 
-SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bbb"'::jsonb ON EMPTY);
- json_query 
-------------
- "b
-(1 row)
-
+SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bb"'::jsonb ON EMPTY);
+ERROR:  value too long for type character(2)
 -- OMIT QUOTES behavior should not be specified when WITH WRAPPER used:
 -- Should fail
 SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
@@ -828,12 +834,6 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10));
  [1, 2]    
 (1 row)
 
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
- json_query 
-------------
- [1,
-(1 row)
-
 SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON);
  json_query 
 ------------
@@ -1353,3 +1353,39 @@ SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz);
  1
 (1 row)
 
+-- Test implicit coercion domain over fixed-legth type specified in RETURNING
+CREATE DOMAIN queryfuncs_char2 AS char(2);
+CREATE DOMAIN queryfuncs_char2_chk AS char(2) CHECK (VALUE NOT IN ('12'));
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR);
+ERROR:  value too long for type character(2)
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 DEFAULT '1' ON ERROR);
+ json_query 
+------------
+ 1 
+(1 row)
+
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2_chk ERROR ON ERROR);
+ERROR:  value too long for type character(2)
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT '1' ON ERROR);
+ json_query 
+------------
+ 1 
+(1 row)
+
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR);
+ERROR:  value too long for type character(2)
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 DEFAULT 1 ON ERROR);
+ json_value 
+------------
+ 1 
+(1 row)
+
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk ERROR ON ERROR);
+ERROR:  value too long for type character(2)
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT 1 ON ERROR);
+ json_value 
+------------
+ 1 
+(1 row)
+
+DROP DOMAIN queryfuncs_char2, queryfuncs_char2_chk;
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index a1f924146e..70bff37a6d 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -266,7 +266,7 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a'));
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
 
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index dc6380141b..0d6482f384 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -53,7 +53,9 @@ SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING int ERROR ON ERROR);
 SELECT JSON_VALUE(jsonb '"aaa"', '$');
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING text);
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5));
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2) ERROR ON ERROR);
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2));
+SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR);
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json);
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb);
 SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json ERROR ON ERROR);
@@ -188,10 +190,11 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING json OMIT QUOTES ERROR ON ERROR);
 SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR);
 
 -- Behavior when a RETURNING type has typmod != -1
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2));
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2) OMIT QUOTES);
-SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bbb' ON EMPTY);
-SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bbb"'::jsonb ON EMPTY);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3));
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) OMIT QUOTES ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bb' ON EMPTY);
+SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bb"'::jsonb ON EMPTY);
 
 -- OMIT QUOTES behavior should not be specified when WITH WRAPPER used:
 -- Should fail
@@ -235,7 +238,6 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb);
 SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb FORMAT JSON);
 SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text);
 SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10));
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
 SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON);
 SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea);
 SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON);
@@ -459,3 +461,16 @@ 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 implicit coercion domain over fixed-legth type specified in RETURNING
+CREATE DOMAIN queryfuncs_char2 AS char(2);
+CREATE DOMAIN queryfuncs_char2_chk AS char(2) CHECK (VALUE NOT IN ('12'));
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 DEFAULT '1' ON ERROR);
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2_chk ERROR ON ERROR);
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT '1' ON ERROR);
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 DEFAULT 1 ON ERROR);
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT 1 ON ERROR);
+DROP DOMAIN queryfuncs_char2, queryfuncs_char2_chk;
-- 
2.43.0