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().

There are two motivations for this change:

1. json_populate_type() coerces to types with typmod such that any
   string values that exceed length limit cause an error instead of
   silent truncation, which is necessary to be standard-conforming.

2. It was possible to end up with a cast expression that doesn't
   support soft handling of errors causing bugs in the of handling
   ON ERROR clause.

JsonExpr.coercion_expr which would store the cast expression is no
longer necessary, so remove.

Bump catversion because stored rules change because of the above
removal.

Reported-by: Alvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Jian He <jian.universality@gmail.com>
Discussion: Discussion: https://postgr.es/m/202405271326.5a5rprki64aw%40alvherre.pgsql

1 files changed, 35 insertions, 153 deletions

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index df766cdec19..560b360644f 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -96,7 +96,6 @@ static Node *transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func);
 static void transformJsonPassingArgs(ParseState *pstate, const char *constructName,
 									 JsonFormatType format, List *args,
 									 List **passing_values, List **passing_names);
-static void coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr);
 static JsonBehavior *transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
 										   JsonBehaviorType default_behavior,
 										   JsonReturning *returning);
@@ -4492,39 +4491,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,
@@ -4548,7 +4515,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			jsexpr->omit_quotes = (func->quotes == JS_QUOTES_OMIT);
 			jsexpr->wrapper = func->wrapper;
 
-			coerceJsonExprOutput(pstate, jsexpr);
+			/*
+			 * Set up to coerce the result value of JsonPathValue() to the
+			 * RETURNING type (default or user-specified), if needed.  Also if
+			 * OMIT QUOTES is specified.
+			 */
+			if (jsexpr->returning->typid != JSONBOID || jsexpr->omit_quotes)
+				jsexpr->use_json_coercion = true;
 
 			/* Assume NULL ON EMPTY when ON EMPTY is not specified. */
 			jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
@@ -4578,7 +4551,18 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			/* Always omit quotes from scalar strings. */
 			jsexpr->omit_quotes = true;
 
-			coerceJsonExprOutput(pstate, jsexpr);
+			/*
+			 * Set up to coerce the result value of JsonPathValue() to the
+			 * RETURNING type (default or user-specified), if needed.
+			 */
+			if (jsexpr->returning->typid != TEXTOID)
+			{
+				if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
+					DomainHasConstraints(jsexpr->returning->typid))
+					jsexpr->use_json_coercion = true;
+				else
+					jsexpr->use_io_coercion = true;
+			}
 
 			/* Assume NULL ON EMPTY when ON EMPTY is not specified. */
 			jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
@@ -4642,121 +4626,6 @@ transformJsonPassingArgs(ParseState *pstate, const char *constructName,
 }
 
 /*
- * Set up to coerce the result value of JSON_VALUE() / JSON_QUERY() to the
- * RETURNING type (default or user-specified), if needed.
- */
-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);
-
-	/*
-	 * Check for cases where the coercion should be handled at runtime, that
-	 * is, without using a cast expression.
-	 */
-	if (jsexpr->op == JSON_VALUE_OP)
-	{
-		/*
-		 * Use cast expressions for types with typmod and domain types.
-		 */
-		if (returning->typmod == -1 &&
-			get_typtype(returning->typid) != TYPTYPE_DOMAIN)
-		{
-			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)
-		{
-			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;
-		}
-	}
-
-	/* 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)
-	{
-		/*
-		 * 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)
-			jsexpr->use_io_coercion = true;
-		else
-			jsexpr->use_json_coercion = true;
-	}
-
-	Assert(jsexpr->coercion_expr != NULL ||
-		   (jsexpr->use_io_coercion != jsexpr->use_json_coercion));
-}
-
-/*
  * Recursively checks if the given expression, or its sub-node in some cases,
  * is valid for using as an ON ERROR / ON EMPTY DEFAULT expression.
  */
@@ -4848,11 +4717,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,