public inbox for [email protected]
help / color / mirror / Atom feedFrom: Amit Langote <[email protected]>
To: jian he <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pgsql: Add more SQL/JSON constructor functions
Date: Fri, 28 Jun 2024 20:53:31 +0900
Message-ID: <CA+HiwqHEn0ZNK9koksrLTpJiwK8Y2pB4R-0tYjQmLy489_4XCQ@mail.gmail.com> (raw)
In-Reply-To: <CACJufxEUioDmFE1SFoGctArK+t9ph5ReE=1skF=5FzMVmu1MJw@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+HiwqE+y_HmGJpKk2_w+CUzmAw=Z5zd6j3_LmCPiGpFyN1gzQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+HiwqHVgMfo0QFc9dQr-VOro_TqaOg9mGxXJTPnTVuRGuXCpA@mail.gmail.com>
<CA+HiwqHCGuzp-v2a6qbJZiDD1wdXay_a_H_fnFs5y25aEbzEkw@mail.gmail.com>
<CACJufxHGitRE61crRLD9YoQMgiV8-FxC9g6kCjKd5fMEaJv6Jw@mail.gmail.com>
<CA+HiwqGXD+D02D=MbnvKgYryMwoa0iQ6=LE4YQzO3790x06FdQ@mail.gmail.com>
<CA+HiwqFre=QTKvkTCupW-R3-6ETJ5ZDEsst4zcS_C16r=cg5Uw@mail.gmail.com>
<CACJufxE_NwpnFgHB-P3qUB0GzUEKKMagpVGfXU=LkQzX1+jOyg@mail.gmail.com>
<CA+HiwqEZDu5hW10TmXEc5O9-M797awHWJnuTbHxemb0Dfft8fQ@mail.gmail.com>
<CA+HiwqFxSZcvZ5mkvD+C7efJqqmAkosnRnX_4u=kXP-Y1odxEw@mail.gmail.com>
<CACJufxEUioDmFE1SFoGctArK+t9ph5ReE=1skF=5FzMVmu1MJw@mail.gmail.com>
On Fri, Jun 28, 2024 at 3:14 PM jian he <[email protected]> wrote:
> On Thu, Jun 27, 2024 at 7:48 PM Amit Langote <[email protected]> wrote:
> > >
> > > I've attempted that in the attached 0001, which removes
> > > JsonExpr.coercion_expr and a bunch of code around it.
> > >
> > > 0002 is now the original patch minus the changes to make
> > > JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE() behave as we would like,
> > > because the changes in 0001 covers them. The changes for JsonBehavior
> > > expression coercion as they were in the last version of the patch are
> > > still needed, but I decided to move those into 0001 so that the
> > > changes for query functions are all in 0001 and those for constructors
> > > in 0002. It would be nice to get rid of that coerce_to_target_type()
> > > call to coerce the "behavior expression" to RETURNING type, but I'm
> > > leaving that as a task for another day.
> >
> > Updated 0001 to remove outdated references, remove some more unnecessary code.
> >
>
> i found some remaining references of "coercion_expr" should be removed.
>
> src/include/nodes/primnodes.h
> /* JsonExpr's collation, if coercion_expr is NULL. */
>
>
> src/include/nodes/execnodes.h
> /*
> * Address of the step to coerce the result value of jsonpath evaluation
> * to the RETURNING type using JsonExpr.coercion_expr. -1 if no coercion
> * is necessary or if either JsonExpr.use_io_coercion or
> * JsonExpr.use_json_coercion is true.
> */
> int jump_eval_coercion;
>
> src/backend/jit/llvm/llvmjit_expr.c
> /* coercion_expr code */
> LLVMPositionBuilderAtEnd(b, b_coercion);
> if (jsestate->jump_eval_coercion >= 0)
> LLVMBuildBr(b, opblocks[jsestate->jump_eval_coercion]);
> else
> LLVMBuildUnreachable(b);
>
>
> src/backend/executor/execExprInterp.c
> /*
> * Checks if an error occurred either when evaluating JsonExpr.coercion_expr or
> * in ExecEvalJsonCoercion(). If so, this sets JsonExprState.error to trigger
> * the ON ERROR handling steps.
> */
> void
> ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op)
> {
> }
>
> if (jbv == NULL)
> {
> /* Will be coerced with coercion_expr, if any. */
> *op->resvalue = (Datum) 0;
> *op->resnull = true;
> }
>
>
> src/backend/executor/execExpr.c
> /*
> * Jump to coerce the NULL using coercion_expr if present. Coercing NULL
> * is only interesting when the RETURNING type is a domain whose
> * constraints must be checked. jsexpr->coercion_expr containing a
> * CoerceToDomain node must have been set in that case.
> */
>
> /*
> * Jump to coerce the NULL using coercion_expr if present. Coercing NULL
> * is only interesting when the RETURNING type is a domain whose
> * constraints must be checked. jsexpr->coercion_expr containing a
> * CoerceToDomain node must have been set in that case.
> */
Thanks for checking.
Will push the attached 0001 and 0002 shortly.
--
Thanks, Amit Langote
Attachments:
[application/octet-stream] v5-0001-SQL-JSON-Fix-coercion-of-constructor-outputs-to-t.patch (5.8K, ../CA+HiwqHEn0ZNK9koksrLTpJiwK8Y2pB4R-0tYjQmLy489_4XCQ@mail.gmail.com/2-v5-0001-SQL-JSON-Fix-coercion-of-constructor-outputs-to-t.patch)
download | inline diff:
From 6f820fbfb68a900e7f478bba89895324d9a64a48 Mon Sep 17 00:00:00 2001
From: Amit Langote <[email protected]>
Date: Wed, 5 Jun 2024 09:13:15 +0900
Subject: [PATCH v5 1/2] SQL/JSON: Fix coercion of constructor outputs to types
with typmod
Ensure SQL/JSON constructor functions that allow specifying the
target type using the RETURNING clause perform implicit cast to
that type. This ensures that output values that exceed the specified
length produce an error rather than being silently truncated. This
behavior conforms to the SQL standard.
Reported-by: Alvaro Herrera <[email protected]>
Reviewed-by: Jian He <[email protected]>
Discussion: https://postgr.es/m/202405271326.5a5rprki64aw%40alvherre.pgsql
---
src/backend/parser/parse_expr.c | 24 +++++++++++++++++++++++-
src/test/regress/expected/sqljson.out | 17 +++++++++++++++++
src/test/regress/sql/sqljson.sql | 12 ++++++++++++
3 files changed, 52 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 233b7b1cc9..df766cdec1 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3583,6 +3583,7 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr,
Node *res;
int location;
Oid exprtype = exprType(expr);
+ int32 baseTypmod = returning->typmod;
/* if output type is not specified or equals to function type, return */
if (!OidIsValid(returning->typid) || returning->typid == exprtype)
@@ -3611,10 +3612,19 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr,
return (Node *) fexpr;
}
+ /*
+ * For domains, consider the base type's typmod to decide whether to setup
+ * an implicit or explicit cast.
+ */
+ if (get_typtype(returning->typid) == TYPTYPE_DOMAIN)
+ (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+
/* try to coerce expression to the output type */
res = coerce_to_target_type(pstate, expr, exprtype,
- returning->typid, returning->typmod,
+ returning->typid, baseTypmod,
+ baseTypmod > 0 ? COERCION_IMPLICIT :
COERCION_EXPLICIT,
+ baseTypmod > 0 ? COERCE_IMPLICIT_CAST :
COERCE_EXPLICIT_CAST,
location);
@@ -3640,6 +3650,7 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type,
JsonConstructorExpr *jsctor = makeNode(JsonConstructorExpr);
Node *placeholder;
Node *coercion;
+ int32 baseTypmod = returning->typmod;
jsctor->args = args;
jsctor->func = fexpr;
@@ -3677,6 +3688,17 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type,
placeholder = (Node *) cte;
}
+ /*
+ * Convert the source expression to text, because coerceJsonFuncExpr()
+ * will create an implicit cast to the RETURNING types with typmod and
+ * there are no implicit casts from json(b) to such types. For domains,
+ * the base type's typmod will be considered, so do so here too.
+ */
+ if (get_typtype(returning->typid) == TYPTYPE_DOMAIN)
+ (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+ if (baseTypmod > 0)
+ placeholder = coerce_to_specific_type(pstate, placeholder, TEXTOID,
+ "JSON_CONSTRUCTOR()");
coercion = coerceJsonFuncExpr(pstate, placeholder, returning, true);
if (coercion != placeholder)
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index cbf8542d8d..4f91e2117e 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1278,3 +1278,20 @@ CREATE OR REPLACE VIEW public.is_json_view AS
'{}'::text IS JSON OBJECT WITH UNIQUE KEYS AS object
FROM generate_series(1, 3) i(i)
DROP VIEW is_json_view;
+-- Test implicit coercion to a fixed-length type specified in RETURNING
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar(2));
+ERROR: value too long for type character varying(2)
+SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING varchar(2)));
+ERROR: value too long for type character varying(2)
+SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING varchar(2)));
+ERROR: value too long for type character varying(2)
+SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING varchar(2)) FROM generate_series(1,1) i;
+ERROR: value too long for type character varying(2)
+SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING varchar(2)) FROM generate_series(1, 1) i;
+ERROR: value too long for type character varying(2)
+-- Now try domain over fixed-length type
+CREATE DOMAIN sqljson_char2 AS char(2) CHECK (VALUE NOT IN ('12'));
+SELECT JSON_SERIALIZE('123' RETURNING sqljson_char2);
+ERROR: value too long for type character(2)
+SELECT JSON_SERIALIZE('12' RETURNING sqljson_char2);
+ERROR: value for domain sqljson_char2 violates check constraint "sqljson_char2_check"
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index e6e20175b0..bb2487e864 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -463,3 +463,15 @@ SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT
\sv is_json_view
DROP VIEW is_json_view;
+
+-- Test implicit coercion to a fixed-length type specified in RETURNING
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar(2));
+SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING varchar(2)));
+SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING varchar(2)));
+SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING varchar(2)) FROM generate_series(1,1) i;
+SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING varchar(2)) FROM generate_series(1, 1) i;
+
+-- Now try domain over fixed-length type
+CREATE DOMAIN sqljson_char2 AS char(2) CHECK (VALUE NOT IN ('12'));
+SELECT JSON_SERIALIZE('123' RETURNING sqljson_char2);
+SELECT JSON_SERIALIZE('12' RETURNING sqljson_char2);
--
2.43.0
[application/octet-stream] v5-0002-SQL-JSON-Always-coerce-JsonExpr-result-at-runtime.patch (43.3K, ../CA+HiwqHEn0ZNK9koksrLTpJiwK8Y2pB4R-0tYjQmLy489_4XCQ@mail.gmail.com/3-v5-0002-SQL-JSON-Always-coerce-JsonExpr-result-at-runtime.patch)
download | inline diff:
From 091027bde219a1712b117dda9679aa2d6481b4ac Mon Sep 17 00:00:00 2001
From: Amit Langote <[email protected]>
Date: Thu, 27 Jun 2024 16:07:39 +0900
Subject: [PATCH v5 2/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().
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 handling the ON
ERROR clause
JsonExpr.coercion_expr which would store the cast expression is no
longer necessary, so remove.
Reviewed-by: Jian He <[email protected]>
Discussion: Discussion: https://postgr.es/m/202405271326.5a5rprki64aw%40alvherre.pgsql
---
src/backend/executor/execExpr.c | 52 ++---
src/backend/executor/execExprInterp.c | 46 ++---
src/backend/jit/llvm/llvmjit_expr.c | 2 +-
src/backend/nodes/nodeFuncs.c | 13 +-
src/backend/parser/parse_expr.c | 188 ++++--------------
src/backend/utils/adt/jsonfuncs.c | 48 +++--
src/include/executor/execExpr.h | 1 +
src/include/nodes/execnodes.h | 5 +-
src/include/nodes/primnodes.h | 8 +-
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 ++-
14 files changed, 210 insertions(+), 305 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 2bf86d06ef..ccd4863778 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);
@@ -4313,13 +4313,15 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
ExprEvalPushStep(state, scratch);
/*
- * Jump to coerce the NULL using coercion_expr if present. Coercing NULL
- * is only interesting when the RETURNING type is a domain whose
- * constraints must be checked. jsexpr->coercion_expr containing a
- * CoerceToDomain node must have been set in that case.
+ * Jump to coerce the NULL using json_populate_type() if needed. Coercing
+ * NULL is only interesting when the RETURNING type is a domain whose
+ * constraints must be checked. jsexpr->use_json_coercion must have been
+ * set in that case.
*/
- if (jsexpr->coercion_expr)
+ if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
+ DomainHasConstraints(jsexpr->returning->typid))
{
+ Assert(jsexpr->use_json_coercion);
scratch->opcode = EEOP_JUMP;
scratch->d.jump.jumpdone = state->steps_len + 1;
ExprEvalPushStep(state, scratch);
@@ -4337,33 +4339,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 +4416,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 +4449,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 +4469,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 +4482,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..918b63f30a 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;
@@ -4316,22 +4322,6 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
jsexpr->column_name);
*op->resnull = (DatumGetPointer(*op->resvalue) == NULL);
-
- /* Handle OMIT QUOTES. */
- if (!*op->resnull && jsexpr->omit_quotes)
- {
- 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:
@@ -4343,7 +4333,7 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
if (jbv == NULL)
{
- /* Will be coerced with coercion_expr, if any. */
+ /* Will be coerced with json_populate_type(), if needed. */
*op->resvalue = (Datum) 0;
*op->resnull = true;
}
@@ -4355,6 +4345,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);
@@ -4364,9 +4359,9 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
* expression if one present. If not, use the input
* function call below to do the coercion.
*/
- *op->resvalue = PointerGetDatum(val_string);
- if (jump_eval_coercion >= 0)
- *op->resvalue = DirectFunctionCall1(textin, *op->resvalue);
+ if (!jsexpr->use_io_coercion)
+ *op->resvalue = DirectFunctionCall1(textin,
+ CStringGetDatum(val_string));
}
}
break;
@@ -4545,13 +4540,14 @@ 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);
}
/*
- * Checks if an error occurred either when evaluating JsonExpr.coercion_expr or
- * in ExecEvalJsonCoercion(). If so, this sets JsonExprState.error to trigger
- * the ON ERROR handling steps.
+ * Checks if an error occurred in ExecEvalJsonCoercion(). If so, this sets
+ * JsonExprState.error to trigger the ON ERROR handling steps.
*/
void
ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op)
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 9e0efd2668..306aea82d3 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2010,7 +2010,7 @@ llvm_compile_expr(ExprState *state)
v_jump_coercion = l_int32_const(lc, jsestate->jump_eval_coercion);
LLVMAddCase(v_switch, v_jump_coercion, b_coercion);
}
- /* coercion_expr code */
+ /* jump_eval_coercion code */
LLVMPositionBuilderAtEnd(b, b_coercion);
if (jsestate->jump_eval_coercion >= 0)
LLVMBuildBr(b, opblocks[jsestate->jump_eval_coercion]);
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 df766cdec1..75987b2aa6 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,
@@ -4641,121 +4625,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,
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index ab5aa0ccb8..48c3f88140 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -447,12 +447,13 @@ static Datum populate_composite(CompositeIOData *io, Oid typid,
HeapTupleHeader defaultval, JsValue *jsv, bool *isnull,
Node *escontext);
static Datum populate_scalar(ScalarIOData *io, Oid typid, int32 typmod, JsValue *jsv,
- bool *isnull, Node *escontext);
+ bool *isnull, Node *escontext, bool omit_quotes);
static void prepare_column_cache(ColumnIOData *column, Oid typid, int32 typmod,
MemoryContext mcxt, bool need_scalar);
static Datum populate_record_field(ColumnIOData *col, Oid typid, int32 typmod,
const char *colname, MemoryContext mcxt, Datum defaultval,
- JsValue *jsv, bool *isnull, Node *escontext);
+ JsValue *jsv, bool *isnull, Node *escontext,
+ bool omit_quotes);
static RecordIOData *allocate_record_info(MemoryContext mcxt, int ncolumns);
static bool JsObjectGetField(JsObject *obj, char *field, JsValue *jsv);
static void populate_recordset_record(PopulateRecordsetState *state, JsObject *obj);
@@ -2622,7 +2623,8 @@ populate_array_element(PopulateArrayContext *ctx, int ndim, JsValue *jsv)
ctx->aio->element_type,
ctx->aio->element_typmod,
NULL, ctx->mcxt, PointerGetDatum(NULL),
- jsv, &element_isnull, ctx->escontext);
+ jsv, &element_isnull, ctx->escontext,
+ false);
/* Nothing to do on an error. */
if (SOFT_ERROR_OCCURRED(ctx->escontext))
return false;
@@ -3119,7 +3121,7 @@ populate_composite(CompositeIOData *io,
*/
static Datum
populate_scalar(ScalarIOData *io, Oid typid, int32 typmod, JsValue *jsv,
- bool *isnull, Node *escontext)
+ bool *isnull, Node *escontext, bool omit_quotes)
{
Datum res;
char *str = NULL;
@@ -3162,7 +3164,9 @@ populate_scalar(ScalarIOData *io, Oid typid, int32 typmod, JsValue *jsv,
{
JsonbValue *jbv = jsv->val.jsonb;
- if (typid == JSONBOID)
+ if (jbv->type == jbvString && omit_quotes)
+ str = pnstrdup(jbv->val.string.val, jbv->val.string.len);
+ else if (typid == JSONBOID)
{
Jsonb *jsonb = JsonbValueToJsonb(jbv); /* directly use jsonb */
@@ -3225,7 +3229,7 @@ populate_domain(DomainIOData *io,
res = populate_record_field(io->base_io,
io->base_typid, io->base_typmod,
colname, mcxt, PointerGetDatum(NULL),
- jsv, isnull, escontext);
+ jsv, isnull, escontext, false);
Assert(!*isnull || SOFT_ERROR_OCCURRED(escontext));
}
@@ -3338,7 +3342,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 +3372,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)
@@ -3379,7 +3395,7 @@ json_populate_type(Datum json_val, Oid json_type,
return populate_record_field(*cache, typid, typmod, NULL, mcxt,
PointerGetDatum(NULL), &jsv, isnull,
- escontext);
+ escontext, omit_quotes);
}
/* recursively populate a record field or an array element from a json/jsonb value */
@@ -3392,7 +3408,8 @@ populate_record_field(ColumnIOData *col,
Datum defaultval,
JsValue *jsv,
bool *isnull,
- Node *escontext)
+ Node *escontext,
+ bool omit_scalar_quotes)
{
TypeCat typcat;
@@ -3426,7 +3443,7 @@ populate_record_field(ColumnIOData *col,
{
case TYPECAT_SCALAR:
return populate_scalar(&col->scalar_io, typid, typmod, jsv,
- isnull, escontext);
+ isnull, escontext, omit_scalar_quotes);
case TYPECAT_ARRAY:
return populate_array(&col->io.array, colname, mcxt, jsv,
@@ -3595,7 +3612,8 @@ populate_record(TupleDesc tupdesc,
nulls[i] ? (Datum) 0 : values[i],
&field,
&nulls[i],
- escontext);
+ escontext,
+ false);
}
res = heap_form_tuple(tupdesc, values, nulls);
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/execnodes.h b/src/include/nodes/execnodes.h
index 8bc421e7c0..b62c96f206 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1052,9 +1052,8 @@ typedef struct JsonExprState
/*
* Address of the step to coerce the result value of jsonpath evaluation
- * to the RETURNING type using JsonExpr.coercion_expr. -1 if no coercion
- * is necessary or if either JsonExpr.use_io_coercion or
- * JsonExpr.use_json_coercion is true.
+ * to the RETURNING type. -1 if no coercion if JsonExpr.use_io_coercion
+ * is true.
*/
int jump_eval_coercion;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4830efc573..ea47652adb 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;
@@ -1854,7 +1848,7 @@ typedef struct JsonExpr
/* KEEP or OMIT QUOTES for singleton scalars returned by JSON_QUERY() */
bool omit_quotes;
- /* JsonExpr's collation, if coercion_expr is NULL. */
+ /* JsonExpr's collation. */
Oid collation;
/* Original JsonFuncExpr's location */
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 dfc7182ba9..5fd43be367 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 6ca17a3d42..074aedb2dd 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
------------
@@ -1369,3 +1369,39 @@ ERROR: invalid ON ERROR behavior
LINE 1: SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);
^
DETAIL: Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for JSON_QUERY().
+-- 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 f1d99a8a73..4594e5b013 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 4586fdb8a4..be5593b332 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);
@@ -464,3 +466,16 @@ SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz);
SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR);
SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);
+
+-- 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
view thread (11+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pgsql: Add more SQL/JSON constructor functions
In-Reply-To: <CA+HiwqHEn0ZNK9koksrLTpJiwK8Y2pB4R-0tYjQmLy489_4XCQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox