From 9154ff35f22fe7e8ce65ffdad757ff94abd79440 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Tue, 30 Aug 2022 00:10:44 +0300 Subject: [PATCH v9 7/9] Remove subtransaction in SQL/JSON execution --- src/backend/executor/execExpr.c | 180 ++-- src/backend/executor/execExprInterp.c | 946 ++++++++++++-------- src/backend/jit/llvm/llvmjit_expr.c | 2 +- src/backend/jit/llvm/llvmjit_types.c | 2 +- src/backend/nodes/nodeFuncs.c | 91 +- src/backend/optimizer/util/clauses.c | 73 +- src/backend/parser/parse_expr.c | 226 ++--- src/backend/utils/adt/jsonpath.c | 8 +- src/include/executor/execExpr.h | 49 +- src/include/nodes/primnodes.h | 36 +- src/include/optimizer/clauses.h | 2 + src/include/utils/jsonpath.h | 3 +- src/test/regress/expected/jsonb_sqljson.out | 327 +++++-- src/test/regress/sql/jsonb_sqljson.sql | 72 +- 14 files changed, 1131 insertions(+), 886 deletions(-) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index d0a57c7aaee..f50e0d703f8 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -86,7 +86,9 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate, FunctionCallInfo fcinfo, AggStatePerTrans pertrans, int transno, int setno, int setoff, bool ishash, bool nullcheck); - +static void ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, + ExprEvalStep *scratch, + Datum *resv, bool *resnull); static ExprState * ExecInitExprInternal(Expr *node, PlanState *parent, ParamListInfo ext_params, @@ -2559,114 +2561,9 @@ ExecInitExprRec(Expr *node, ExprState *state, } case T_JsonExpr: - { - JsonExpr *jexpr = castNode(JsonExpr, node); - JsonExprState *jsestate = palloc0(sizeof(JsonExprState)); - ListCell *argexprlc; - ListCell *argnamelc; - - scratch.opcode = EEOP_JSONEXPR; - scratch.d.jsonexpr.jsestate = jsestate; - - jsestate->jsexpr = jexpr; - - jsestate->formatted_expr = - palloc(sizeof(*jsestate->formatted_expr)); - - ExecInitExprRec((Expr *) jexpr->formatted_expr, state, - &jsestate->formatted_expr->value, - &jsestate->formatted_expr->isnull); - - jsestate->pathspec = - palloc(sizeof(*jsestate->pathspec)); - - ExecInitExprRec((Expr *) jexpr->path_spec, state, - &jsestate->pathspec->value, - &jsestate->pathspec->isnull); - - jsestate->res_expr = - palloc(sizeof(*jsestate->res_expr)); - - jsestate->result_expr = jexpr->result_coercion - ? ExecInitExprWithCaseValue((Expr *) jexpr->result_coercion->expr, - state->parent, - &jsestate->res_expr->value, - &jsestate->res_expr->isnull) - : NULL; - - jsestate->default_on_empty = !jexpr->on_empty ? NULL : - ExecInitExpr((Expr *) jexpr->on_empty->default_expr, - state->parent); - - jsestate->default_on_error = - ExecInitExpr((Expr *) jexpr->on_error->default_expr, - state->parent); - - if (jexpr->omit_quotes || - (jexpr->result_coercion && jexpr->result_coercion->via_io)) - { - Oid typinput; - - /* lookup the result type's input function */ - getTypeInputInfo(jexpr->returning->typid, &typinput, - &jsestate->input.typioparam); - fmgr_info(typinput, &jsestate->input.func); - } - - jsestate->args = NIL; - - forboth(argexprlc, jexpr->passing_values, - argnamelc, jexpr->passing_names) - { - Expr *argexpr = (Expr *) lfirst(argexprlc); - String *argname = lfirst_node(String, argnamelc); - JsonPathVariableEvalContext *var = palloc(sizeof(*var)); - - var->name = pstrdup(argname->sval); - var->typid = exprType((Node *) argexpr); - var->typmod = exprTypmod((Node *) argexpr); - var->estate = ExecInitExpr(argexpr, state->parent); - var->econtext = NULL; - var->mcxt = NULL; - var->evaluated = false; - var->value = (Datum) 0; - var->isnull = true; - - jsestate->args = - lappend(jsestate->args, var); - } - - jsestate->cache = NULL; - - if (jexpr->coercions) - { - JsonCoercion **coercion; - struct JsonCoercionState *cstate; - Datum *caseval; - bool *casenull; - - jsestate->coercion_expr = - palloc(sizeof(*jsestate->coercion_expr)); - - caseval = &jsestate->coercion_expr->value; - casenull = &jsestate->coercion_expr->isnull; - - for (cstate = &jsestate->coercions.null, - coercion = &jexpr->coercions->null; - coercion <= &jexpr->coercions->composite; - coercion++, cstate++) - { - cstate->coercion = *coercion; - cstate->estate = *coercion ? - ExecInitExprWithCaseValue((Expr *) (*coercion)->expr, - state->parent, - caseval, casenull) : NULL; - } - } - - ExprEvalPushStep(state, &scratch); - break; - } + ExecInitJsonExpr(castNode(JsonExpr, node), state, &scratch, + resv, resnull); + break; default: elog(ERROR, "unrecognized node type: %d", @@ -4257,3 +4154,68 @@ ExecBuildParamSetEqual(TupleDesc desc, return state; } + +static void +ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch, + Datum *resv, bool *resnull) +{ + JsonExprState *jsestate; + ListCell *argexprlc; + ListCell *argnamelc; + + /* JSON_TABLE preudo-function returns context item as a result */ + if (jexpr->op == JSON_TABLE_OP) + { + ExecInitExprRec((Expr *) jexpr->formatted_expr, state, + resv, resnull); + return; + } + + jsestate = palloc0(sizeof(JsonExprState)); + jsestate->jsexpr = jexpr; + + scratch->opcode = EEOP_JSONEXPR; + scratch->d.jsonexpr.jsestate = jsestate; + + ExecInitExprRec((Expr *) jexpr->formatted_expr, state, + &jsestate->formatted_expr.value, + &jsestate->formatted_expr.isnull); + + ExecInitExprRec((Expr *) jexpr->path_spec, state, + &jsestate->pathspec.value, + &jsestate->pathspec.isnull); + + if (jexpr->on_empty) + jsestate->default_on_empty = + ExecInitExpr((Expr *) jexpr->on_empty->default_expr, + state->parent); + + jsestate->default_on_error = + ExecInitExpr((Expr *) jexpr->on_error->default_expr, + state->parent); + + jsestate->args = NIL; + + forboth(argexprlc, jexpr->passing_values, + argnamelc, jexpr->passing_names) + { + Expr *argexpr = (Expr *) lfirst(argexprlc); + String *argname = lfirst_node(String, argnamelc); + JsonPathVariableEvalContext *var = palloc(sizeof(*var)); + + var->name = pstrdup(argname->sval); + var->typid = exprType((Node *) argexpr); + var->typmod = exprTypmod((Node *) argexpr); + var->estate = ExecInitExpr(argexpr, state->parent); + var->econtext = NULL; + var->mcxt = NULL; + var->evaluated = false; + var->value = (Datum) 0; + var->isnull = true; + + jsestate->args = + lappend(jsestate->args, var); + } + + ExprEvalPushStep(state, scratch); +} diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 636794ca6f1..45c648817ba 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -75,6 +75,7 @@ #include "utils/date.h" #include "utils/datum.h" #include "utils/expandedrecord.h" +#include "utils/float.h" #include "utils/json.h" #include "utils/jsonb.h" #include "utils/jsonfuncs.h" @@ -1844,7 +1845,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_CASE(EEOP_JSONEXPR) { /* too complex for an inline implementation */ - ExecEvalJson(state, op, econtext); + ExecEvalJsonExpr(state, op, econtext); EEO_NEXT(); } @@ -4700,96 +4701,6 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, *op->resnull = isnull; } -/* - * Evaluate a JSON error/empty behavior result. - */ -static Datum -ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior, - ExprState *default_estate, bool *is_null) -{ - *is_null = false; - - switch (behavior->btype) - { - case JSON_BEHAVIOR_EMPTY_ARRAY: - return JsonbPGetDatum(JsonbMakeEmptyArray()); - - case JSON_BEHAVIOR_EMPTY_OBJECT: - return JsonbPGetDatum(JsonbMakeEmptyObject()); - - case JSON_BEHAVIOR_TRUE: - return BoolGetDatum(true); - - case JSON_BEHAVIOR_FALSE: - return BoolGetDatum(false); - - case JSON_BEHAVIOR_NULL: - case JSON_BEHAVIOR_UNKNOWN: - case JSON_BEHAVIOR_EMPTY: - *is_null = true; - return (Datum) 0; - - case JSON_BEHAVIOR_DEFAULT: - return ExecEvalExpr(default_estate, econtext, is_null); - - default: - elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype); - return (Datum) 0; - } -} - -/* - * Evaluate a coercion of a JSON item to the target type. - */ -static Datum -ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext, - Datum res, bool *isNull, void *p, bool *error) -{ - ExprState *estate = p; - JsonExprState *jsestate; - - if (estate) /* coerce using specified expression */ - return ExecEvalExpr(estate, econtext, isNull); - - jsestate = op->d.jsonexpr.jsestate; - - if (jsestate->jsexpr->op != JSON_EXISTS_OP) - { - JsonCoercion *coercion = jsestate->jsexpr->result_coercion; - JsonExpr *jexpr = jsestate->jsexpr; - Jsonb *jb = *isNull ? NULL : DatumGetJsonbP(res); - - if ((coercion && coercion->via_io) || - (jexpr->omit_quotes && !*isNull && - JB_ROOT_IS_SCALAR(jb))) - { - /* strip quotes and call typinput function */ - char *str = *isNull ? NULL : JsonbUnquote(jb); - - return InputFunctionCall(&jsestate->input.func, str, - jsestate->input.typioparam, - jexpr->returning->typmod); - } - else if (coercion && coercion->via_populate) - return json_populate_type(res, JSONBOID, - jexpr->returning->typid, - jexpr->returning->typmod, - &jsestate->cache, - econtext->ecxt_per_query_memory, - isNull); - } - - if (jsestate->result_expr) - { - jsestate->res_expr->value = res; - jsestate->res_expr->isnull = *isNull; - - res = ExecEvalExpr(jsestate->result_expr, econtext, isNull); - } - - return res; -} - /* * Evaluate a JSON path variable caching computed value. */ @@ -4844,84 +4755,438 @@ EvalJsonPathVar(void *cxt, char *varName, int varNameLen, } /* - * Prepare SQL/JSON item coercion to the output type. Returned a datum of the - * corresponding SQL type and a pointer to the coercion state. + * Check whether we need to override default coercion in + * JSON_QUERY(OMIT QUOTES) case. */ -Datum -ExecPrepareJsonItemCoercion(JsonbValue *item, - JsonReturning *returning, - struct JsonCoercionsState *coercions, - struct JsonCoercionState **pcoercion) +static bool +ExecJsonQueryNeedsIOCoercion(JsonExpr *jsexpr, Datum res, bool isnull) { - struct JsonCoercionState *coercion; - Datum res; - JsonbValue buf; + if (jsexpr->omit_quotes && !isnull) + { + Jsonb *jb = DatumGetJsonbP(res); + JsonbValue jbv; + + /* Coerce string items via I/O in OMIT QUOTES case */ + return JsonbExtractScalar(&jb->root, &jbv) && + jbv.type == jbvString; + } + + return false; +} - if (item->type == jbvBinary && - JsonContainerIsScalar(item->val.binary.data)) +/* Coerce C string to text, varchar(N), or bpchar(N) */ +static Datum +ExecJsonStringCoercion(const char *str, int32 len, Oid typid, int32 typmod) +{ + if (typid == BYTEAOID) { - bool res PG_USED_FOR_ASSERTS_ONLY; + text *txt = cstring_to_text_with_len(str, len); + NameData encoding = {0}; + + /* UTF8 is the only one supported encoding */ + strcpy(NameStr(encoding), "UTF8"); - res = JsonbExtractScalar(item->val.binary.data, &buf); - item = &buf; - Assert(res); + return DirectFunctionCall2(pg_convert_to, + PointerGetDatum(txt), + PointerGetDatum(&encoding)); } + else if (typid == TEXTOID || typid == JSONOID || typmod <= VARHDRSZ) + return PointerGetDatum(cstring_to_text_with_len(str, len)); + else + { + char *txt; + int32 size; + + len = VARHDRSZ + len; + + if (typid == BPCHAROID) + size = typmod; + else + size = Min(len, typmod); + + txt = palloc(size); + SET_VARSIZE(txt, size); + + memcpy(VARDATA(txt), str, Min(size, len) - VARHDRSZ); + + if (len < size) + memset(txt + len, ' ', size - len); - /* get coercion state reference and datum of the corresponding SQL type */ + return PointerGetDatum(txt); + } +} + +/* Coerce SQL/JSON item to text */ +static Datum +ExecJsonCoercionToText(PGFunction outfunc, Datum value, Oid typid, int32 typmod) +{ + char *str = DatumGetCString(DirectFunctionCall1(outfunc, value)); + + return ExecJsonStringCoercion(str, strlen(str), typid, typmod); +} + +/* Coerce datetime SQL/JSON item to the output typid */ +static Datum +ExecJsonDatetimeCoercion(Datum val, Oid val_typid, Oid typid, int32 typmod, + bool *isnull, bool *error) +{ + if (val_typid == typid) + return val; + + /* + * XXX coercion to text is done using output functions, and they + * are mutable for non-time[tz] types due to using of DateStyle. + * We can pass USE_ISO_DATES, which is used inside jsonpath, to + * make these coercions and JSON_VALUE(RETURNING text) immutable. + * + * XXX Also timestamp[tz] output functions can throw "out of range" + * error, but this error seem to be not possible. + */ + switch (val_typid) + { + case DATEOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(date_out, val, typid, typmod); + else if (typid == DATEOID) + return val; + else if (typid == TIMESTAMPOID) + { + int overflow = 0; + Timestamp ts = + date2timestamp_opt_overflow(DatumGetDateADT(val), + error ? &overflow : NULL); + + if (overflow) + { + *error = true; + return (Datum) 0; + } + + return TimestampGetDatum(ts); + } + else if (typid == TIMESTAMPTZOID) + { + int overflow = 0; + TimestampTz res = + date2timestamptz_opt_overflow(DatumGetDateADT(val), + error ? &overflow : NULL); + + if (overflow) + { + *error = true; + return (Datum) 0; + } + + return TimestampTzGetDatum(res); + } + else + break; /* No cast */ + + case TIMEOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(time_out, val, typid, typmod); + else if (typid == TIMEOID) + return val; + else if (typid == TIMETZOID) + return DirectFunctionCall1(time_timetz, val); + else + break; /* No cast */ + + case TIMETZOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(timetz_out, val, typid, typmod); + else if (typid == TIMETZOID) + return val; + else if (typid == TIMEOID) + return DirectFunctionCall1(timetz_time, val); + else + break; + + case TIMESTAMPOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(timestamp_out, val, typid, typmod); + else if (typid == TIMESTAMPOID) + return val; + else if (typid == DATEOID) + return timestamp_date_opt_error(DatumGetTimestamp(val), error); + else if (typid == TIMEOID) + { + TimeADT time = + timestamp_time_opt_error(DatumGetTimestamp(val), + isnull, error); + + if ((error && *error) || *isnull) + return (Datum) 0; + else + return TimeADTGetDatum(time); + } + else if (typid == TIMESTAMPTZOID) + { + int overflow = 0; + TimestampTz tstz = + timestamp2timestamptz_opt_overflow(DatumGetTimestamp(val), + error ? &overflow : NULL); + + if (overflow) + { + *error = true; + return (Datum) 0; + } + + return TimestampTzGetDatum(tstz); + } + else + break; /* No cast */ + + case TIMESTAMPTZOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(timestamptz_out, val, typid, typmod); + else if (typid == TIMESTAMPTZOID) + return val; + else if (typid == DATEOID) + return timestamptz_date_opt_error(DatumGetTimestampTz(val), error); + else if (typid == TIMEOID) + { + TimeADT time = + timestamptz_time_opt_error(DatumGetTimestampTz(val), + isnull, error); + + if ((error && *error) || *isnull) + return (Datum) 0; + else + return TimeADTGetDatum(time); + } + else if (typid == TIMETZOID) + { + TimeTzADT *result = + timestamptz_timetz_opt_error(DatumGetTimestampTz(val), error); + + if ((error && *error) || !result) + { + *isnull = true; + return (Datum) 0; + } + + return TimeTzADTPGetDatum(result); + } + else if (typid == TIMESTAMPOID) + { + Timestamp ts = + timestamptz2timestamp_opt_error(DatumGetTimestampTz(val), + error); + + if (error && *error) + return (Datum) 0; + + return TimestampGetDatum(ts); + } + else + break; /* No cast */ + + default: + elog(ERROR, "unexpected jsonb datetime type oid %u", val_typid); + break; + } + + if (!error) + ereport(ERROR, + (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE), + errmsg("SQL/JSON item cannot be cast to target type"))); + + *error = true; + *isnull = true; + + return (Datum) 0; +} + +/* Coerce boolean SQL/JSON item or JSON_EXISTS result to the output type */ +static bool +ExecJsonBoolCoercion(bool val, Oid typid, int32 typmod, Datum *res) +{ + if (typid == BOOLOID) + *res = BoolGetDatum(val); + else if (typid == INT4OID) + /* We only have cast bool::int4 in the catalog. */ + *res = Int32GetDatum(val ? 1 : 0); + else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + /* + * bool::text returns 'true' / 'false', + * boolout() returns 't' / 'f'. + */ + *res = ExecJsonStringCoercion(val ? "true" : "false", val ? 4 : 5, + typid, typmod); + else + return false; + + return true; +} + +static Datum +JsonbPGetTextDatum(Jsonb *jb) +{ + char *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb)); + Datum res = CStringGetTextDatum(str); + + pfree(str); + return res; +} + +/* Coerce SQL/JSON item to the output typid */ +static Datum +ExecJsonValueCoercion(JsonbValue *item, Oid typid, int32 typmod, + bool *isnull, bool *error) +{ + *isnull = false; + + /* Special case for json and jsonb output types */ + if (typid == JSONBOID) + return JsonbPGetDatum(JsonbValueToJsonb(item)); + + if (typid == JSONOID) + return JsonbPGetTextDatum(JsonbValueToJsonb(item)); + + /* + * Coercion method and set of supported output types are determined + * by the item type. + */ switch (item->type) { case jbvNull: - coercion = &coercions->null; - res = (Datum) 0; - break; + Assert(0); /* must be handled by the caller */ + *isnull = true; + return (Datum) 0; case jbvString: - coercion = &coercions->string; - res = PointerGetDatum(cstring_to_text_with_len(item->val.string.val, - item->val.string.len)); - break; + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + { + return ExecJsonStringCoercion(item->val.string.val, + item->val.string.len, + typid, typmod); + } + else if (typid == INT2OID || typid == INT4OID || typid == INT8OID) + { + char *str = pnstrdup(item->val.string.val, + item->val.string.len); + + if (error) + { + int64 val = pg_strtoint64_opt_error(str, error); + + if (*error) + return (Datum) 0; + + if (typid == INT2OID) + { + if (val <= PG_INT16_MAX || val >= PG_INT16_MIN) + return Int16GetDatum((int16) val); + } + else if (typid == INT4OID) + { + if (val <= PG_INT32_MAX || val >= PG_INT32_MIN) + return Int32GetDatum((int32) val); + } + else + return Int64GetDatum(val); + + *error = true; + return (Datum) 0; + } + else if (typid == INT2OID) + return Int16GetDatum(pg_strtoint16(str)); + else if (typid == INT4OID) + return Int32GetDatum(pg_strtoint32(str)); + else + return Int64GetDatum(pg_strtoint64(str)); + } + else if (typid == BOOLOID) + { + return BoolGetDatum(boolin_opt_error(item->val.string.val, + item->val.string.len, + error)); + } + else + { + char *str = pnstrdup(item->val.string.val, + item->val.string.len); + + if (typid == FLOAT4OID) + return Float4GetDatum(float4in_opt_error(str, error)); + else if (typid == FLOAT8OID) + return Float8GetDatum( + float8in_internal_opt_error(str, NULL, + "double precision", + str, error)); + else if (typid == NUMERICOID) + return NumericGetDatum(numeric_in_opt_error(str, typmod, error)); + else if (typid == DATEOID) + return DateADTGetDatum(date_in_opt_error(str, error)); + else if (typid == TIMEOID) + return TimeADTGetDatum(time_in_opt_error(str, typmod, error)); + else if (typid == TIMETZOID) + return TimeTzADTPGetDatum(timetz_in_opt_error(str, typmod, error)); + else if (typid == TIMESTAMPOID) + return TimestampGetDatum(timestamp_in_opt_error(str, typmod, error)); + else if (typid == TIMESTAMPTZOID) + return TimestampTzGetDatum(timestamptz_in_opt_error(str, typmod, error)); + else + break; /* No cast */ + } case jbvNumeric: - coercion = &coercions->numeric; - res = NumericGetDatum(item->val.numeric); - break; + { + Numeric num = item->val.numeric; + + if (typid == NUMERICOID) + return NumericGetDatum(num); + else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(numeric_out, NumericGetDatum(num), typid, typmod); + else if (typid == INT2OID) + return Int16GetDatum(numeric_int2_opt_error(num, error)); + else if (typid == INT4OID) + return Int32GetDatum(numeric_int4_opt_error(num, error)); + else if (typid == INT8OID) + return Int64GetDatum(numeric_int8_opt_error(num, error)); + else if (typid == FLOAT4OID || typid == FLOAT8OID) + { + /* + * XXX numeric_float8() also uses I/O coercion, but + * it has special handling of Inf and NaN. + */ + Datum cstr = DirectFunctionCall1(numeric_out, + NumericGetDatum(num)); + char *str = DatumGetCString(cstr); - case jbvBool: - coercion = &coercions->boolean; - res = BoolGetDatum(item->val.boolean); - break; + if (typid == FLOAT4OID) + return Float4GetDatum(float4in_opt_error(str, error)); + else + return Float8GetDatum( + float8in_internal_opt_error(str, NULL, + "double precision", + str, error)); + } + else + break; /* No cast */ + } - case jbvDatetime: - res = item->val.datetime.value; - switch (item->val.datetime.typid) + case jbvBool: { - case DATEOID: - coercion = &coercions->date; - break; - case TIMEOID: - coercion = &coercions->time; - break; - case TIMETZOID: - coercion = &coercions->timetz; - break; - case TIMESTAMPOID: - coercion = &coercions->timestamp; - break; - case TIMESTAMPTZOID: - coercion = &coercions->timestamptz; - break; - default: - elog(ERROR, "unexpected jsonb datetime type oid %u", - item->val.datetime.typid); - return (Datum) 0; + Datum res; + + if (ExecJsonBoolCoercion(item->val.boolean, typid, typmod, &res)) + return res; + + break; /* No cast */ } - break; + + case jbvDatetime: + return ExecJsonDatetimeCoercion(item->val.datetime.value, + item->val.datetime.typid, + typid, typmod, isnull, error); case jbvArray: case jbvObject: case jbvBinary: - coercion = &coercions->composite; - res = JsonbPGetDatum(JsonbValueToJsonb(item)); + Assert(0); /* non-scalars must be rejected by JsonPathValue() */ break; default: @@ -4929,99 +5194,100 @@ ExecPrepareJsonItemCoercion(JsonbValue *item, return (Datum) 0; } - *pcoercion = coercion; + if (!error) + ereport(ERROR, + (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE), + errmsg("SQL/JSON item cannot be cast to target type"))); - return res; -} + *error = true; + *isnull = true; -typedef Datum (*JsonFunc) (ExprEvalStep *op, ExprContext *econtext, - Datum item, bool *resnull, void *p, bool *error); + return (Datum) 0; +} +/* + * Evaluate a JSON error/empty behavior and coerce result to the output + * type. + */ static Datum -ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op, - ExprContext *econtext, - Datum res, bool *resnull, - void *p, bool *error, bool subtrans) +ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior, + ExprState *default_estate, + Oid ret_typid, int32 ret_typmod, bool *is_null) { - MemoryContext oldcontext; - ResourceOwner oldowner; - - if (!subtrans) - /* No need to use subtransactions. */ - return func(op, econtext, res, resnull, p, error); + *is_null = false; - /* - * We should catch exceptions of category ERRCODE_DATA_EXCEPTION and - * execute the corresponding ON ERROR behavior then. - */ - oldcontext = CurrentMemoryContext; - oldowner = CurrentResourceOwner; + switch (behavior->btype) + { + case JSON_BEHAVIOR_EMPTY_ARRAY: + if (ret_typid == JSONBOID) + return JsonbPGetDatum(JsonbMakeEmptyArray()); + else + { + Assert(ret_typid == JSONOID || ret_typid == TEXTOID || + ret_typid == VARCHAROID || ret_typid == BPCHAROID || + ret_typid == BYTEAOID); - Assert(error); + return ExecJsonStringCoercion("[]", 2, ret_typid, ret_typmod); + } - BeginInternalSubTransaction(NULL); - /* Want to execute expressions inside function's memory context */ - MemoryContextSwitchTo(oldcontext); + case JSON_BEHAVIOR_EMPTY_OBJECT: + if (ret_typid == JSONBOID) + return JsonbPGetDatum(JsonbMakeEmptyObject()); + else + { + Assert(ret_typid == JSONOID || ret_typid == TEXTOID || + ret_typid == VARCHAROID || ret_typid == BPCHAROID || + ret_typid == BYTEAOID); - PG_TRY(); - { - res = func(op, econtext, res, resnull, p, error); + return ExecJsonStringCoercion("{}", 2, ret_typid, ret_typmod); + } - /* Commit the inner transaction, return to outer xact context */ - ReleaseCurrentSubTransaction(); - MemoryContextSwitchTo(oldcontext); - CurrentResourceOwner = oldowner; - } - PG_CATCH(); - { - ErrorData *edata; - int ecategory; + case JSON_BEHAVIOR_TRUE: + case JSON_BEHAVIOR_FALSE: + { + Datum res; + bool ok = + ExecJsonBoolCoercion(behavior->btype == JSON_BEHAVIOR_TRUE, + ret_typid, ret_typmod, &res); - /* Save error info in oldcontext */ - MemoryContextSwitchTo(oldcontext); - edata = CopyErrorData(); - FlushErrorState(); + Assert(ok); /* returning type must be checked in parser */ - /* Abort the inner transaction */ - RollbackAndReleaseCurrentSubTransaction(); - MemoryContextSwitchTo(oldcontext); - CurrentResourceOwner = oldowner; + return res; + } - ecategory = ERRCODE_TO_CATEGORY(edata->sqlerrcode); + case JSON_BEHAVIOR_NULL: + case JSON_BEHAVIOR_UNKNOWN: + case JSON_BEHAVIOR_EMPTY: + *is_null = true; + return (Datum) 0; - if (ecategory != ERRCODE_DATA_EXCEPTION && /* jsonpath and other data - * errors */ - ecategory != ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION) /* domain errors */ - ReThrowError(edata); + case JSON_BEHAVIOR_DEFAULT: + /* + * Execute DEFAULT expression. + * Coercion is not needed here, because expression is + * already coerced to the target type by the parser. + */ + return ExecEvalExpr(default_estate, econtext, is_null); - res = (Datum) 0; - *error = true; + default: + elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype); + return (Datum) 0; } - PG_END_TRY(); - - return res; } - -typedef struct -{ - JsonPath *path; - bool *error; - bool coercionInSubtrans; -} ExecEvalJsonExprContext; - static Datum -ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, - Datum item, bool *resnull, void *pcxt, - bool *error) +ExecEvalJsonExprInternal(ExprState *state, + JsonExprState *jsestate, ExprContext *econtext, + JsonPath *path, Datum item, bool *resnull, + bool *error) { - ExecEvalJsonExprContext *cxt = pcxt; - JsonPath *path = cxt->path; - JsonExprState *jsestate = op->d.jsonexpr.jsestate; JsonExpr *jexpr = jsestate->jsexpr; - ExprState *estate = NULL; bool empty = false; Datum res = (Datum) 0; + Oid ret_typid = jexpr->returning->typid; + int32 ret_typmod = jexpr->returning->typmod; + + *resnull = true; switch (jexpr->op) { @@ -5029,70 +5295,96 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error, jsestate->args); if (error && *error) - { - *resnull = true; return (Datum) 0; - } + + if (empty) + break; + *resnull = !DatumGetPointer(res); - break; + + /* Override default coercion in OMIT QUOTES case */ + if (ExecJsonQueryNeedsIOCoercion(jexpr, res, *resnull)) + { + char *str = JsonbUnquote(DatumGetJsonbP(res)); + + if (ret_typid == JSONBOID) + { + res = jsonb_from_cstring(str, strlen(str), false, error); + + if (error && *error) + return (Datum) 0; + + return res; + } + else if (ret_typid == JSONOID) + { + text *json = cstring_to_text(str); + JsonLexContext *lex; + JsonParseErrorType result; + + /* validate it */ + lex = makeJsonLexContext(json, false); + result = pg_parse_json(lex, &nullSemAction); + + if (result != JSON_SUCCESS) + { + if (error) + { + *error = true; + return (Datum) 0; + } + + json_ereport_error(result, lex); + } + + return PointerGetDatum(json); + } + else if (ret_typid == TEXTOID || + ret_typid == VARCHAROID || + ret_typid == BPCHAROID || + ret_typid == BYTEAOID) + return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod); + } + else if (ret_typid == JSONBOID) + return res; + else if (ret_typid == JSONOID || ret_typid == TEXTOID) + return JsonbPGetTextDatum(DatumGetJsonbP(res)); + else if (ret_typid == VARCHAROID || ret_typid == BPCHAROID || + ret_typid == BYTEAOID) + { + Jsonb *jb = DatumGetJsonbP(res); + char *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb)); + + return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod); + } + + Assert(0); /* unsupported output type */ + *error = *resnull = true; + return (Datum) 0; case JSON_VALUE_OP: { - struct JsonCoercionState *jcstate; JsonbValue *jbv = JsonPathValue(item, path, &empty, error, jsestate->args); if (error && *error) return (Datum) 0; - if (!jbv) /* NULL or empty */ + if (empty) break; - Assert(!empty); - - *resnull = false; + if (!jbv) + return (Datum) 0; /* NULL */ - /* coerce scalar item to the output type */ - if (jexpr->returning->typid == JSONOID || - jexpr->returning->typid == JSONBOID) - { - /* Use result coercion from json[b] to the output type */ - res = JsonbPGetDatum(JsonbValueToJsonb(jbv)); - break; - } + Assert(jbv->type != jbvNull); - /* Use coercion from SQL/JSON item type to the output type */ - res = ExecPrepareJsonItemCoercion(jbv, - jsestate->jsexpr->returning, - &jsestate->coercions, - &jcstate); + res = ExecJsonValueCoercion(jbv, ret_typid, ret_typmod, + resnull, error); - if (jcstate->coercion && - (jcstate->coercion->via_io || - jcstate->coercion->via_populate)) - { - if (error) - { - *error = true; - return (Datum) 0; - } - - /* - * Coercion via I/O means here that the cast to the target - * type simply does not exist. - */ - ereport(ERROR, - (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE), - errmsg("SQL/JSON item cannot be cast to target type"))); - } - else if (!jcstate->estate) - return res; /* no coercion */ + if (error && *error) + return (Datum) 0; - /* coerce using specific expression */ - estate = jcstate->estate; - jsestate->coercion_expr->value = res; - jsestate->coercion_expr->isnull = *resnull; - break; + return res; } case JSON_EXISTS_OP: @@ -5101,113 +5393,68 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, jsestate->args, error); - *resnull = error && *error; - res = BoolGetDatum(exists); - - if (!jsestate->result_expr) - return res; + if (!error || !*error) + { + /* Should succeed, output type is checked by parser */ + (void) ExecJsonBoolCoercion(exists, ret_typid, ret_typmod, &res); + *resnull = false; + } - /* coerce using result expression */ - estate = jsestate->result_expr; - jsestate->res_expr->value = res; - jsestate->res_expr->isnull = *resnull; - break; + return res; } - case JSON_TABLE_OP: - *resnull = false; - return item; - default: elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op); return (Datum) 0; } - if (empty) - { - Assert(jexpr->on_empty); /* it is not JSON_EXISTS */ + Assert(empty); + Assert(jexpr->on_empty); /* it is not JSON_EXISTS */ - if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR) + if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR) + { + if (error) { - if (error) - { - *error = true; - return (Datum) 0; - } - - ereport(ERROR, - (errcode(ERRCODE_NO_SQL_JSON_ITEM), - errmsg("no SQL/JSON item"))); + *error = true; + return (Datum) 0; } - if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT) - - /* - * Execute DEFAULT expression as a coercion expression, because - * its result is already coerced to the target type. - */ - estate = jsestate->default_on_empty; - else - /* Execute ON EMPTY behavior */ - res = ExecEvalJsonBehavior(econtext, jexpr->on_empty, - jsestate->default_on_empty, - resnull); + ereport(ERROR, + (errcode(ERRCODE_NO_SQL_JSON_ITEM), + errmsg("no SQL/JSON item"))); } - return ExecEvalJsonExprSubtrans(ExecEvalJsonExprCoercion, op, econtext, - res, resnull, estate, error, - cxt->coercionInSubtrans); -} - -bool -ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr, - struct JsonCoercionsState *coercions) -{ - if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR) - return false; - - if (jsexpr->op == JSON_EXISTS_OP && !jsexpr->result_coercion) - return false; - - if (!coercions) - return true; - - return false; + /* Execute ON EMPTY behavior */ + return ExecEvalJsonBehavior(econtext, jexpr->on_empty, + jsestate->default_on_empty, + ret_typid, ret_typmod, resnull); } /* ---------------------------------------------------------------- - * ExecEvalJson + * ExecEvalJsonExpr * ---------------------------------------------------------------- */ void -ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) +ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext) { - ExecEvalJsonExprContext cxt; JsonExprState *jsestate = op->d.jsonexpr.jsestate; JsonExpr *jexpr = jsestate->jsexpr; Datum item; - Datum res = (Datum) 0; + Datum res; JsonPath *path; ListCell *lc; bool error = false; - bool needSubtrans; bool throwErrors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR; - *op->resnull = true; /* until we get a result */ - *op->resvalue = (Datum) 0; - - if (jsestate->formatted_expr->isnull || jsestate->pathspec->isnull) + if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull) { - /* execute domain checks for NULLs */ - (void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull, - NULL, NULL); - - Assert(*op->resnull); + *op->resnull = true; + *op->resvalue = (Datum) 0; return; } - item = jsestate->formatted_expr->value; - path = DatumGetJsonPathP(jsestate->pathspec->value); + item = jsestate->formatted_expr.value; + path = DatumGetJsonPathP(jsestate->pathspec.value); /* reset JSON path variable contexts */ foreach(lc, jsestate->args) @@ -5218,29 +5465,20 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) var->evaluated = false; } - needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &jsestate->coercions); - - cxt.path = path; - cxt.error = throwErrors ? NULL : &error; - cxt.coercionInSubtrans = !needSubtrans && !throwErrors; - Assert(!needSubtrans || cxt.error); - - res = ExecEvalJsonExprSubtrans(ExecEvalJsonExpr, op, econtext, item, - op->resnull, &cxt, cxt.error, - needSubtrans); + res = ExecEvalJsonExprInternal(state, jsestate, econtext, + path, item, op->resnull, + throwErrors ? NULL : &error); if (error) { + Assert(!throwErrors); + /* Execute ON ERROR behavior */ res = ExecEvalJsonBehavior(econtext, jexpr->on_error, jsestate->default_on_error, + jexpr->returning->typid, + jexpr->returning->typmod, op->resnull); - - /* result is already coerced in DEFAULT behavior case */ - if (jexpr->on_error->btype != JSON_BEHAVIOR_DEFAULT) - res = ExecEvalJsonExprCoercion(op, econtext, res, - op->resnull, - NULL, NULL); } *op->resvalue = res; diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index bd3965143da..fd72630f5e6 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -2408,7 +2408,7 @@ llvm_compile_expr(ExprState *state) break; case EEOP_JSONEXPR: - build_EvalXFunc(b, mod, "ExecEvalJson", + build_EvalXFunc(b, mod, "ExecEvalJsonExpr", v_state, op, v_econtext); LLVMBuildBr(b, opblocks[opno + 1]); break; diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c index 373471ad27f..37fe64654b6 100644 --- a/src/backend/jit/llvm/llvmjit_types.c +++ b/src/backend/jit/llvm/llvmjit_types.c @@ -135,7 +135,7 @@ void *referenced_functions[] = ExecEvalXmlExpr, ExecEvalJsonConstructor, ExecEvalJsonIsPredicate, - ExecEvalJson, + ExecEvalJsonExpr, MakeExpandedObjectReadOnlyInternal, slot_getmissingattrs, slot_getsomeattrs_int, diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index c334daae392..e0240beeeab 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -266,9 +266,6 @@ exprType(const Node *expr) case T_JsonExpr: type = ((const JsonExpr *) expr)->returning->typid; break; - case T_JsonCoercion: - type = exprType(((const JsonCoercion *) expr)->expr); - break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr)); type = InvalidOid; /* keep compiler quiet */ @@ -507,8 +504,6 @@ exprTypmod(const Node *expr) return ((const JsonConstructorExpr *) expr)->returning->typmod; case T_JsonExpr: return ((JsonExpr *) expr)->returning->typmod; - case T_JsonCoercion: - return exprTypmod(((const JsonCoercion *) expr)->expr); default: break; } @@ -1010,14 +1005,12 @@ exprCollation(const Node *expr) case T_JsonExpr: { JsonExpr *jexpr = (JsonExpr *) expr; - JsonCoercion *coercion = jexpr->result_coercion; - if (!coercion) - coll = InvalidOid; - else if (coercion->expr) - coll = exprCollation(coercion->expr); - else if (coercion->via_io || coercion->via_populate) - coll = coercion->collation; + if (jexpr->returning->typid == TEXTOID || + jexpr->returning->typid == CHAROID || + jexpr->returning->typid == VARCHAROID || + jexpr->returning->typid == BPCHAROID) + coll = jexpr->collation; else coll = InvalidOid; } @@ -1255,14 +1248,12 @@ exprSetCollation(Node *expr, Oid collation) case T_JsonExpr: { JsonExpr *jexpr = (JsonExpr *) expr; - JsonCoercion *coercion = jexpr->result_coercion; - if (!coercion) - Assert(!OidIsValid(collation)); - else if (coercion->expr) - exprSetCollation(coercion->expr, collation); - else if (coercion->via_io || coercion->via_populate) - coercion->collation = collation; + if (jexpr->returning->typid == TEXTOID || + jexpr->returning->typid == CHAROID || + jexpr->returning->typid == VARCHAROID || + jexpr->returning->typid == BPCHAROID) + jexpr->collation = collation; else Assert(!OidIsValid(collation)); } @@ -2507,8 +2498,6 @@ expression_tree_walker(Node *node, if (walker(jexpr->formatted_expr, context)) return true; - if (walker(jexpr->result_coercion, context)) - return true; if (walker(jexpr->passing_values, context)) return true; /* we assume walker doesn't care about passing_names */ @@ -2517,36 +2506,6 @@ expression_tree_walker(Node *node, return true; if (walker(jexpr->on_error->default_expr, context)) return true; - if (walker(jexpr->coercions, context)) - return true; - } - break; - case T_JsonCoercion: - return walker(((JsonCoercion *) node)->expr, context); - case T_JsonItemCoercions: - { - JsonItemCoercions *coercions = (JsonItemCoercions *) node; - - if (walker(coercions->null, context)) - return true; - if (walker(coercions->string, context)) - return true; - if (walker(coercions->numeric, context)) - return true; - if (walker(coercions->boolean, context)) - return true; - if (walker(coercions->date, context)) - return true; - if (walker(coercions->time, context)) - return true; - if (walker(coercions->timetz, context)) - return true; - if (walker(coercions->timestamp, context)) - return true; - if (walker(coercions->timestamptz, context)) - return true; - if (walker(coercions->composite, context)) - return true; } break; default: @@ -3576,7 +3535,6 @@ expression_tree_mutator(Node *node, FLATCOPY(newnode, jexpr, JsonExpr); MUTATE(newnode->path_spec, jexpr->path_spec, Node *); MUTATE(newnode->formatted_expr, jexpr->formatted_expr, Node *); - MUTATE(newnode->result_coercion, jexpr->result_coercion, JsonCoercion *); MUTATE(newnode->passing_values, jexpr->passing_values, List *); /* assume mutator does not care about passing_names */ if (newnode->on_empty) @@ -3587,35 +3545,6 @@ expression_tree_mutator(Node *node, return (Node *) newnode; } break; - case T_JsonCoercion: - { - JsonCoercion *coercion = (JsonCoercion *) node; - JsonCoercion *newnode; - - FLATCOPY(newnode, coercion, JsonCoercion); - MUTATE(newnode->expr, coercion->expr, Node *); - return (Node *) newnode; - } - break; - case T_JsonItemCoercions: - { - JsonItemCoercions *coercions = (JsonItemCoercions *) node; - JsonItemCoercions *newnode; - - FLATCOPY(newnode, coercions, JsonItemCoercions); - MUTATE(newnode->null, coercions->null, JsonCoercion *); - MUTATE(newnode->string, coercions->string, JsonCoercion *); - MUTATE(newnode->numeric, coercions->numeric, JsonCoercion *); - MUTATE(newnode->boolean, coercions->boolean, JsonCoercion *); - MUTATE(newnode->date, coercions->date, JsonCoercion *); - MUTATE(newnode->time, coercions->time, JsonCoercion *); - MUTATE(newnode->timetz, coercions->timetz, JsonCoercion *); - MUTATE(newnode->timestamp, coercions->timestamp, JsonCoercion *); - MUTATE(newnode->timestamptz, coercions->timestamptz, JsonCoercion *); - MUTATE(newnode->composite, coercions->composite, JsonCoercion *); - return (Node *) newnode; - } - break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 533df86ff77..6eb923892f4 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -411,6 +411,26 @@ contain_mutable_functions_walker(Node *node, void *context) { JsonExpr *jexpr = castNode(JsonExpr, node); Const *cnst; + bool returns_datetime; + + /* + * Input fuctions for datetime types are stable. They can be + * called in JSON_VALUE(), when the resulting SQL/JSON is a + * string. + */ + if (jexpr->returning->typid == DATEOID || + jexpr->returning->typid == TIMEOID || + jexpr->returning->typid == TIMETZOID || + jexpr->returning->typid == TIMESTAMPOID || + jexpr->returning->typid == TIMESTAMPTZOID) + return true; + + /* + * pg_convert_to(), which is used for implementation of + * JSON_QUERY(RETURNING bytea FORMAT JSON), is stable. + */ + if (jexpr->returning->typid == BYTEAOID) + return true; if (!IsA(jexpr->path_spec, Const)) return true; @@ -421,8 +441,32 @@ contain_mutable_functions_walker(Node *node, void *context) if (cnst->constisnull) return false; - return jspIsMutable(DatumGetJsonPathP(cnst->constvalue), - jexpr->passing_names, jexpr->passing_values); + if (jspIsMutable(DatumGetJsonPathP(cnst->constvalue), + jexpr->passing_names, jexpr->passing_values, + &returns_datetime)) + return true; + + if (returns_datetime && jexpr->op == JSON_VALUE_OP) + { + /* + * Some datetime types have mutable output functions, + * so if returning string types whole expression is mutable. + * TODO check individual datetime type if it is known. + * + * Other non-JSON output types have no conversion from + * datetime types. + * + * Datetime returning types were checked above, no need to + * check them here. But conversion between them also can + * be mutable. + */ + if (jexpr->returning->typid == TEXTOID || + jexpr->returning->typid == VARCHAROID || + jexpr->returning->typid == BPCHAROID) + return true; + } + + return false; } if (IsA(node, SQLValueFunction)) @@ -896,18 +940,6 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context) context, 0); } - /* JsonExpr is parallel-unsafe if subtransactions can be used. */ - else if (IsA(node, JsonExpr)) - { - JsonExpr *jsexpr = (JsonExpr *) node; - - if (ExecEvalJsonNeedsSubTransaction(jsexpr, NULL)) - { - context->max_hazard = PROPARALLEL_UNSAFE; - return true; - } - } - /* Recurse to check arguments */ return expression_tree_walker(node, max_parallel_hazard_walker, @@ -5323,3 +5355,16 @@ pull_paramids_walker(Node *node, Bitmapset **context) return expression_tree_walker(node, pull_paramids_walker, (void *) context); } + +bool +expr_can_throw_errors(Node *expr) +{ + if (!expr) + return false; + + if (IsA(expr, Const)) + return false; + + /* TODO consider more cases */ + return true; +} diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index fabb5f72076..c02530a3671 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -22,6 +22,7 @@ #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" +#include "optimizer/clauses.h" #include "optimizer/optimizer.h" #include "parser/analyze.h" #include "parser/parse_agg.h" @@ -4108,7 +4109,6 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func) /* format is determined by context item type */ format = exprType(jsexpr->formatted_expr) == JSONBOID ? JS_FORMAT_JSONB : JS_FORMAT_JSON; - jsexpr->result_coercion = NULL; jsexpr->omit_quotes = false; jsexpr->format = func->common->expr->format; @@ -4169,40 +4169,6 @@ assignDefaultJsonReturningType(Node *context_item, JsonFormat *context_format, ret->typmod = -1; } -/* - * Try to coerce expression to the output type or - * use json_populate_type() for composite, array and domain types or - * use coercion via I/O. - */ -static JsonCoercion * -coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning) -{ - char typtype; - JsonCoercion *coercion = makeNode(JsonCoercion); - - coercion->expr = coerceJsonFuncExpr(pstate, expr, returning, false); - - if (coercion->expr) - { - if (coercion->expr == expr) - coercion->expr = NULL; - - return coercion; - } - - typtype = get_typtype(returning->typid); - - if (returning->typid == RECORDOID || - typtype == TYPTYPE_COMPOSITE || - typtype == TYPTYPE_DOMAIN || - type_is_array(returning->typid)) - coercion->via_populate = true; - else - coercion->via_io = true; - - return coercion; -} - /* * Transform a JSON output clause of JSON_VALUE and JSON_QUERY. */ @@ -4210,8 +4176,6 @@ static void transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, JsonExpr *jsexpr) { - Node *expr = jsexpr->formatted_expr; - jsexpr->returning = transformJsonOutput(pstate, func->output, false); /* JSON_VALUE returns text by default */ @@ -4225,14 +4189,41 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, { JsonReturning ret; - if (func->op == JSON_VALUE_OP && - jsexpr->returning->typid != JSONOID && - jsexpr->returning->typid != JSONBOID) + if (func->op == JSON_VALUE_OP) { - /* Forced coercion via I/O for JSON_VALUE for non-JSON types */ - jsexpr->result_coercion = makeNode(JsonCoercion); - jsexpr->result_coercion->expr = NULL; - jsexpr->result_coercion->via_io = true; + /* + * Only a limited list of output types is supported in + * JSON_VALUE() now. + */ + switch (jsexpr->returning->typid) + { + case JSONBOID: + case JSONOID: + case TEXTOID: + case VARCHAROID: + case BPCHAROID: + case BOOLOID: + case NUMERICOID: + case INT2OID: + case INT4OID: + case INT8OID: + case FLOAT4OID: + case FLOAT8OID: + case DATEOID: + case TIMEOID: + case TIMETZOID: + case TIMESTAMPOID: + case TIMESTAMPTZOID: + break; /* Ok */ + + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("returning type %s is not supported in JSON_VALUE()", + format_type_be(jsexpr->returning->typid)), + parser_coercion_errposition(pstate, func->location, (Node *) jsexpr))); + } + return; } @@ -4241,13 +4232,23 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, if (ret.typid != jsexpr->returning->typid || ret.typmod != jsexpr->returning->typmod) { - Node *placeholder = makeCaseTestExpr(expr); - - Assert(((CaseTestExpr *) placeholder)->typeId == ret.typid); - Assert(((CaseTestExpr *) placeholder)->typeMod == ret.typmod); + switch (jsexpr->returning->typid) + { + case JSONBOID: + case JSONOID: + case TEXTOID: + case VARCHAROID: + case BPCHAROID: + case BYTEAOID: + break; - jsexpr->result_coercion = coerceJsonExpr(pstate, placeholder, - jsexpr->returning); + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("returning type %s is not supported in JSON_QUERY()", + format_type_be(jsexpr->returning->typid)), + parser_coercion_errposition(pstate, func->location, (Node *) jsexpr))); + } } } else @@ -4259,8 +4260,10 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, * Coerce an expression in JSON DEFAULT behavior to the target output type. */ static Node * -coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr) +coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, + bool is_on_empty, Node *defexpr) { + Node *orig_defexpr = defexpr; int location; Oid exprtype; @@ -4290,63 +4293,31 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr) format_type_be(jsexpr->returning->typid)), parser_errposition(pstate, location))); - return defexpr; -} + /* Don't allow unsafe DEFAUL ON EMPTY expressions, unless ERROR ON ERROR */ + if (!is_on_empty || + jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR) + return defexpr; -/* - * Initialize SQL/JSON item coercion from the SQL type "typid" to the target - * "returning" type. - */ -static JsonCoercion * -initJsonItemCoercion(ParseState *pstate, Oid typid, - const JsonReturning *returning) -{ - Node *expr; + if (!expr_can_throw_errors(defexpr)) + return defexpr; - if (typid == UNKNOWNOID) + /* Try to simplify expression if there is non-empty coercion */ + if (defexpr != orig_defexpr && + !expr_can_throw_errors(orig_defexpr)) { - expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid); - } - else - { - CaseTestExpr *placeholder = makeNode(CaseTestExpr); + defexpr = eval_const_expressions(NULL, defexpr); - placeholder->typeId = typid; - placeholder->typeMod = -1; - placeholder->collation = InvalidOid; - - expr = (Node *) placeholder; + if (!expr_can_throw_errors(defexpr)) + return defexpr; } - return coerceJsonExpr(pstate, expr, returning); -} + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("unsafe DEFAULT ON EMPTY expressions are not supported"), + errhint("Use ERROR ON ERROR clause or try to simplify expression into constant-like form."), + parser_errposition(pstate, location))); -static void -initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions, - const JsonReturning *returning, Oid contextItemTypeId) -{ - struct - { - JsonCoercion **coercion; - Oid typid; - } *p, - coercionTypids[] = - { - {&coercions->null, UNKNOWNOID}, - {&coercions->string, TEXTOID}, - {&coercions->numeric, NUMERICOID}, - {&coercions->boolean, BOOLOID}, - {&coercions->date, DATEOID}, - {&coercions->time, TIMEOID}, - {&coercions->timetz, TIMETZOID}, - {&coercions->timestamp, TIMESTAMPOID}, - {&coercions->timestamptz, TIMESTAMPTZOID}, - {&coercions->composite, contextItemTypeId}, - {NULL, InvalidOid} - }; - - for (p = coercionTypids; p->coercion; p++) - *p->coercion = initJsonItemCoercion(pstate, p->typid, returning); + return NULL; } /* @@ -4370,17 +4341,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) jsexpr->returning->format->encoding = JS_ENC_DEFAULT; jsexpr->on_empty->default_expr = - coerceDefaultJsonExpr(pstate, jsexpr, + coerceDefaultJsonExpr(pstate, jsexpr, true, jsexpr->on_empty->default_expr); jsexpr->on_error->default_expr = - coerceDefaultJsonExpr(pstate, jsexpr, + coerceDefaultJsonExpr(pstate, jsexpr, false, jsexpr->on_error->default_expr); - jsexpr->coercions = makeNode(JsonItemCoercions); - initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning, - exprType(contextItemExpr)); - break; case JSON_QUERY_OP: @@ -4389,11 +4356,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) transformJsonFuncExprOutput(pstate, func, jsexpr); jsexpr->on_empty->default_expr = - coerceDefaultJsonExpr(pstate, jsexpr, + coerceDefaultJsonExpr(pstate, jsexpr, true, jsexpr->on_empty->default_expr); jsexpr->on_error->default_expr = - coerceDefaultJsonExpr(pstate, jsexpr, + coerceDefaultJsonExpr(pstate, jsexpr, false, jsexpr->on_error->default_expr); jsexpr->wrapper = func->wrapper; @@ -4409,6 +4376,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT; jsexpr->returning->format->encoding = JS_ENC_DEFAULT; + /* Coerce intermediate boolean result to the output type if needed */ if (!OidIsValid(jsexpr->returning->typid)) { jsexpr->returning->typid = BOOLOID; @@ -4416,32 +4384,21 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) } else if (jsexpr->returning->typid != BOOLOID) { - CaseTestExpr *placeholder = makeNode(CaseTestExpr); - int location = exprLocation((Node *) jsexpr); - - placeholder->typeId = BOOLOID; - placeholder->typeMod = -1; - placeholder->collation = InvalidOid; - - jsexpr->result_coercion = makeNode(JsonCoercion); - jsexpr->result_coercion->expr = - coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID, - jsexpr->returning->typid, - jsexpr->returning->typmod, - COERCION_EXPLICIT, - COERCE_IMPLICIT_CAST, - location); - - if (!jsexpr->result_coercion->expr) - 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))); + switch (jsexpr->returning->typid) + { + case INT4OID: + case TEXTOID: + case VARCHAROID: + case BPCHAROID: + break; - if (jsexpr->result_coercion->expr == (Node *) placeholder) - jsexpr->result_coercion->expr = NULL; + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("returning type %s is not supported in JSON_EXISTS()", + format_type_be(jsexpr->returning->typid)), + parser_coercion_errposition(pstate, func->location, (Node *) jsexpr))); + } } break; @@ -4457,7 +4414,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) errmsg("JSON_TABLE() is not yet implemented for the json type"), errhint("Try casting the argument to jsonb"), parser_errposition(pstate, func->location))); - break; } diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c index da9df4ae766..9c9f9ad703f 100644 --- a/src/backend/utils/adt/jsonpath.c +++ b/src/backend/utils/adt/jsonpath.c @@ -1318,9 +1318,11 @@ jspIsMutableWalker(JsonPathItem *jpi, JsonPathMutableContext *cxt) * Check whether jsonpath expression is immutable or not. */ bool -jspIsMutable(JsonPath *path, List *varnames, List *varexprs) +jspIsMutable(JsonPath *path, List *varnames, List *varexprs, + bool *returns_datetime) { JsonPathMutableContext cxt; + JsonPathDatatypeStatus status; JsonPathItem jpi; cxt.varnames = varnames; @@ -1330,7 +1332,9 @@ jspIsMutable(JsonPath *path, List *varnames, List *varexprs) cxt.mutable = false; jspInit(&jpi, path); - jspIsMutableWalker(&jpi, &cxt); + status = jspIsMutableWalker(&jpi, &cxt); + + *returns_datetime = status != jpdsNonDateTime; return cxt.mutable; } diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index c8ef917ffe0..adabf57c97b 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -762,43 +762,14 @@ typedef struct JsonExprState { JsonExpr *jsexpr; /* original expression node */ - struct - { - FmgrInfo func; /* typinput function for output type */ - Oid typioparam; - } input; /* I/O info for output type */ - NullableDatum - *formatted_expr, /* formatted context item value */ - *res_expr, /* result item */ - *coercion_expr, /* input for JSON item coercion */ - *pathspec; /* path specification value */ + formatted_expr, /* formatted context item value */ + coercion_expr, /* input for JSON item coercion */ + pathspec; /* path specification value */ - ExprState *result_expr; /* coerced to output type */ ExprState *default_on_empty; /* ON EMPTY DEFAULT expression */ ExprState *default_on_error; /* ON ERROR DEFAULT expression */ List *args; /* passing arguments */ - - void *cache; /* cache for json_populate_type() */ - - struct JsonCoercionsState - { - struct JsonCoercionState - { - JsonCoercion *coercion; /* coercion expression */ - ExprState *estate; /* coercion expression state */ - } null, - string, - numeric , - boolean, - date, - time, - timetz, - timestamp, - timestamptz, - composite; - } coercions; /* states for coercion from SQL/JSON item - * types directly to the output type */ } JsonExprState; /* functions in execExpr.c */ @@ -860,18 +831,8 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext, TupleTableSlot *slot); extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, ExprContext *econtext); -extern void ExecEvalJson(ExprState *state, ExprEvalStep *op, - ExprContext *econtext); -extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item, - JsonReturning *returning, - struct JsonCoercionsState *coercions, - struct JsonCoercionState **pjcstate); -extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr, - struct JsonCoercionsState *); -extern Datum ExecEvalExprPassingCaseValue(ExprState *estate, - ExprContext *econtext, bool *isnull, - Datum caseval_datum, - bool caseval_isnull); +extern void ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, + ExprContext *econtext); extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup, ExprContext *aggcontext); diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 3aa96bb6855..14fbb32ce9c 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1551,39 +1551,6 @@ typedef struct JsonBehavior Node *default_expr; /* default expression, if any */ } JsonBehavior; -/* - * JsonCoercion - - * coercion from SQL/JSON item types to SQL types - */ -typedef struct JsonCoercion -{ - NodeTag type; - Node *expr; /* resulting expression coerced to target type */ - bool via_populate; /* coerce result using json_populate_type()? */ - bool via_io; /* coerce result using type input function? */ - Oid collation; /* collation for coercion via I/O or populate */ -} JsonCoercion; - -/* - * JsonItemCoercions - - * expressions for coercion from SQL/JSON item types directly to the - * output SQL type - */ -typedef struct JsonItemCoercions -{ - NodeTag type; - JsonCoercion *null; - JsonCoercion *string; - JsonCoercion *numeric; - JsonCoercion *boolean; - JsonCoercion *date; - JsonCoercion *time; - JsonCoercion *timetz; - JsonCoercion *timestamp; - JsonCoercion *timestamptz; - JsonCoercion *composite; /* arrays and objects */ -} JsonItemCoercions; - /* * JsonExpr - * transformed representation of JSON_VALUE(), JSON_QUERY(), JSON_EXISTS() @@ -1593,7 +1560,6 @@ typedef struct JsonExpr Expr xpr; JsonExprOp op; /* json function ID */ Node *formatted_expr; /* formatted context item expression */ - JsonCoercion *result_coercion; /* resulting coercion to RETURNING type */ JsonFormat *format; /* context item format (JSON/JSONB) */ Node *path_spec; /* JSON path specification expression */ List *passing_names; /* PASSING argument names */ @@ -1601,8 +1567,8 @@ typedef struct JsonExpr JsonReturning *returning; /* RETURNING clause type/format info */ JsonBehavior *on_empty; /* ON EMPTY behavior */ JsonBehavior *on_error; /* ON ERROR behavior */ - JsonItemCoercions *coercions; /* coercions for JSON_VALUE */ JsonWrapper wrapper; /* WRAPPER for JSON_QUERY */ + Oid collation; /* OID of collation, or InvalidOid if none */ bool omit_quotes; /* KEEP/OMIT QUOTES for JSON_QUERY */ int location; /* token location, or -1 if unknown */ } JsonExpr; diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index 6c5203dc448..4d61b88c161 100644 --- a/src/include/optimizer/clauses.h +++ b/src/include/optimizer/clauses.h @@ -55,4 +55,6 @@ extern Query *inline_set_returning_function(PlannerInfo *root, extern Bitmapset *pull_paramids(Expr *expr); +extern bool expr_can_throw_errors(Node *expr); + #endif /* CLAUSES_H */ diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index 8e79b8dc9f0..e9134b7eab4 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -177,7 +177,8 @@ extern bool jspGetBool(JsonPathItem *v); extern char *jspGetString(JsonPathItem *v, int32 *len); extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from, JsonPathItem *to, int i); -extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs); +extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs, + bool *returns_datetime); extern const char *jspOperationName(JsonPathItemType type); diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index ef496110af3..0e07bbc858f 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -184,11 +184,11 @@ SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR); (1 row) SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb); -ERROR: cannot cast type boolean to jsonb +ERROR: returning type jsonb is not supported in JSON_EXISTS() LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb); ^ SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4); -ERROR: cannot cast type boolean to real +ERROR: returning type real is not supported in JSON_EXISTS() LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4); ^ -- JSON_VALUE @@ -242,7 +242,9 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING text); /* jsonb bytea ??? */ SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR); -ERROR: SQL/JSON item cannot be cast to target type +ERROR: returning type bytea is not supported in JSON_VALUE() +LINE 2: SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ... + ^ SELECT JSON_VALUE(jsonb '1.23', '$'); json_value ------------ @@ -349,14 +351,129 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; 03-01-2017 (1 row) --- Test NULL checks execution in domain types +SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float4); + json_value +------------ + 1.23 +(1 row) + +SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4); + json_value +------------ + 1.23 +(1 row) + +SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float4); + json_value +------------ + NaN +(1 row) + +SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4); + json_value +------------ + -Infinity +(1 row) + +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR); + json_value +------------ + -1 +(1 row) + +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR); +ERROR: invalid input syntax for type real: "err" +SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float8); + json_value +------------ + 1.23 +(1 row) + +SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8); + json_value +------------ + 1.23 +(1 row) + +SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float8); + json_value +------------ + NaN +(1 row) + +SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8); + json_value +------------ + -Infinity +(1 row) + +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR); + json_value +------------ + -1 +(1 row) + +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR); +ERROR: invalid input syntax for type double precision: "err" +SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING numeric); + json_value +------------ + 1.23 +(1 row) + +SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric); + json_value +------------ + 1.23 +(1 row) + +SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING numeric); + json_value +------------ + NaN +(1 row) + +SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric); + json_value +------------ + -Infinity +(1 row) + +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR); + json_value +------------ + -1 +(1 row) + +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR); +ERROR: invalid input syntax for type numeric: "err" +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_... + ^ +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); json_value ------------ @@ -478,11 +595,9 @@ SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a); (1 row) SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point); - json_value ------------- - (1,2) -(1 row) - +ERROR: returning type point is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )... + ^ -- Test timestamptz passing and output SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts); json_value @@ -514,6 +629,36 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 + "2018-02-21T02:34:56+00:00" (1 row) +-- test errors in DEFAULT ON EMPTY expressions +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int + DEFAULT 1 / x ON EMPTY + DEFAULT 2 ON ERROR) +FROM (VALUES (1::int), (0)) x(x); +ERROR: unsafe DEFAULT ON EMPTY expressions are not supported +LINE 2: DEFAULT 1 / x ON EMPTY + ^ +HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form. +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY); + json_value +------------ + -1 +(1 row) + +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY); + json_value +------------ + -1 +(1 row) + +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY); +ERROR: invalid input syntax for type integer: "err" +LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON E... + ^ +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY); +ERROR: unsafe DEFAULT ON EMPTY expressions are not supported +LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ... + ^ +HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form. -- JSON_QUERY SELECT JSON_QUERY(js, '$'), @@ -852,49 +997,35 @@ FROM CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]); CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]); SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec); - json_query ------------------------------------------------------ - (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",) -(1 row) - +ERROR: returning type sqljsonb_rec is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "... + ^ SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa); - unnest ------------------------- - {"a": 1, "b": ["foo"]} - {"a": 2, "c": {}} - 123 -(3 rows) - +ERROR: returning type sqljsonb_rec is not supported in JSON_QUERY() +LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "... + ^ SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca); - a | t | js | jb | jsa ----+-------------+----+------------+----- - 1 | ["foo", []] | | | - 2 | | | [{}, true] | -(2 rows) - +ERROR: returning type sqljsonb_reca is not supported in JSON_QUERY() +LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "... + ^ -- Extension: array types returning SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER); - json_query --------------- - {1,2,NULL,3} -(1 row) - +ERROR: returning type integer[] is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING i... + ^ SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[])); - a | t | js | jb | jsa ----+-------------+----+------------+----- - 1 | ["foo", []] | | | - 2 | | | [{}, true] | -(2 rows) - +ERROR: returning type sqljsonb_rec[] is not supported in JSON_QUERY() +LINE 1: SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo"... + ^ -- Extension: domain types returning SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null); - json_query ------------- - 1 -(1 row) - +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb... + ^ SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb... + ^ -- Test timestamptz passing and output SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts); json_query @@ -1019,6 +1150,25 @@ ERROR: functions in index expression must be marked IMMUTABLE CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x)); ERROR: functions in index expression must be marked IMMUTABLE CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x)); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date)); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time)); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz)); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp)); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz)); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()')); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**')); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x)); +ERROR: functions in index expression must be marked IMMUTABLE +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable +CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable DROP TABLE test_jsonb_mutability; -- JSON_TABLE -- Should fail (JSON_TABLE can be used only in FROM clause) @@ -1048,7 +1198,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$' (1 row) -- JSON_TABLE: basic functionality -CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); SELECT * FROM (VALUES @@ -1069,7 +1218,6 @@ FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -1085,29 +1233,26 @@ FROM exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, - jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$' + jsb2q jsonb PATH '$' OMIT QUOTES ) ) jt ON true; - js | id | id2 | int | text | char(4) | bool | numeric | domain | js | jb | jst | jsc | jsv | jsb | jsbq | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 | js2 | jsb2w | jsb2q | ia | ta | jba ----------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------+----+----+----- - 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | | - [] | | | | | | | | | | | | | | | | | | | | | | | | | | | - {} | 1 | 1 | | | | | | | {} | {} | {} | {} | {} | {} | {} | | | f | 0 | | false | {} | [{}] | {} | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | | f | 0 | | false | 1.23 | [1.23] | 1.23 | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | 2 | "2" | "2" | "2" | "2" | "2" | "2" | 2 | | | f | 0 | | false | "2" | ["2"] | 2 | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | aaaaaaa | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | | | f | 0 | | false | "aaaaaaa" | ["aaaaaaa"] | | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | foo | foo | | | | "foo" | "foo" | "foo" | "foo | "foo | "foo" | | | | f | 0 | | false | "foo" | ["foo"] | | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | | | | | | | null | null | null | null | null | null | null | | | f | 0 | | false | null | [null] | null | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 0 | false | fals | f | | false | false | false | false | fals | fals | false | false | | | f | 0 | | false | false | [false] | false | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | 1 | true | true | t | | true | true | true | true | true | true | true | true | | | f | 0 | | false | true | [true] | true | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | | | | | | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 | 123 | t | 1 | 1 | true | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | [1,2] | [1,2 | | | [1,2] | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] | | | f | 0 | | false | "[1,2]" | ["[1,2]"] | [1, 2] | | | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | 11 | | "str" | "str | | | "str" | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" | | | f | 0 | | false | "\"str\"" | ["\"str\""] | "str" | | | + js | id | id2 | int | text | char(4) | bool | numeric | js | jb | jst | jsc | jsv | jsb | jsbq | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 | js2 | jsb2w | jsb2q +---------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+-------------- + 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 + [] | | | | | | | | | | | | | | | | | | | | | | | + {} | 1 | 1 | | | | | | {} | {} | {} | {} | {} | {} | {} | | | f | 0 | | false | {} | [{}] | {} + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | | f | 0 | | false | 1.23 | [1.23] | 1.23 + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | "2" | "2" | "2" | "2" | "2" | "2" | 2 | | | f | 0 | | false | "2" | ["2"] | 2 + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | | | f | 0 | | false | "aaaaaaa" | ["aaaaaaa"] | + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | foo | foo | | | "foo" | "foo" | "foo" | "foo | "foo | "foo" | | | | f | 0 | | false | "foo" | ["foo"] | + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | | | | | | null | null | null | null | null | null | null | | | f | 0 | | false | null | [null] | null + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 0 | false | fals | f | | false | false | false | fals | fals | false | false | | | f | 0 | | false | false | [false] | false + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | 1 | true | true | t | | true | true | true | true | true | true | true | | | f | 0 | | false | true | [true] | true + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | | | | | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 | 123 | t | 1 | 1 | true | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | [1,2] | [1,2 | | | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] | | | f | 0 | | false | "[1,2]" | ["[1,2]"] | [1, 2] + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | 11 | | "str" | "str | | | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" | | | f | 0 | | false | "\"str\"" | ["\"str\""] | "str" (14 rows) -- JSON_TABLE: Test backward parsing @@ -1123,7 +1268,6 @@ SELECT * FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -1139,9 +1283,6 @@ SELECT * FROM js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS ( a1 int, NESTED PATH '$[*]' AS "p1 1" COLUMNS ( @@ -1168,7 +1309,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS "json_table"."char(4)", "json_table".bool, "json_table"."numeric", - "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, @@ -1184,9 +1324,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, - "json_table".ia, - "json_table".ta, - "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, @@ -1205,7 +1342,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', - domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -1221,9 +1357,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, - ia integer[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS ( a1 integer PATH '$."a1"', @@ -1248,15 +1381,14 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))) ) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table Function Scan on "json_table" - Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 - Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))) + Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))) (3 rows) DROP VIEW jsonb_table_view; -DROP DOMAIN jsonb_test_domain; -- JSON_TABLE: ON EMPTY/ON ERROR behavior SELECT * FROM @@ -1318,15 +1450,15 @@ 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 +ERROR: returning type smallint is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXI... ^ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to bigint +ERROR: returning type bigint is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXI... ^ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to real +ERROR: returning type real is not supported in JSON_EXISTS() 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')); @@ -1336,11 +1468,11 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')) (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to json +ERROR: returning type json is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXI... ^ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to jsonb +ERROR: returning type jsonb is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX... ^ -- JSON_TABLE: nested paths and plans @@ -2102,11 +2234,14 @@ set parallel_leader_participation = off; -- Should be non-parallel due to subtransactions EXPLAIN (COSTS OFF) SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Seq Scan on test_parallel_jsonb_value -(2 rows) + QUERY PLAN +------------------------------------------------------------------ + Finalize Aggregate + -> Gather + Workers Planned: 4 + -> Partial Aggregate + -> Parallel Seq Scan on test_parallel_jsonb_value +(5 rows) SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value; sum diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index fff25374808..43a194ad09f 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -82,12 +82,43 @@ SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234; SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; --- Test NULL checks execution in domain types +SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float4); +SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4); +SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float4); +SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4); +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR); +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR); + +SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float8); +SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8); +SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float8); +SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8); +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR); +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR); + +SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING numeric); +SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric); +SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING numeric); +SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric); +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR); +SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR); + +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); + +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); + +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); + +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR); SELECT JSON_VALUE(jsonb '{}', '$'); @@ -133,6 +164,17 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 + SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json); SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb); +-- test errors in DEFAULT ON EMPTY expressions +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int + DEFAULT 1 / x ON EMPTY + DEFAULT 2 ON ERROR) +FROM (VALUES (1::int), (0)) x(x); + +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY); +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY); +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY); +SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY); + -- JSON_QUERY SELECT @@ -318,6 +360,21 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ? CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x)); CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x)); CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x)); + +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date)); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time)); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz)); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp)); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz)); + +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()')); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**')); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x)); +CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable + +CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable + DROP TABLE test_jsonb_mutability; -- JSON_TABLE @@ -338,8 +395,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$' COLUMNS (item int PATH '$', foo int)) bar; -- JSON_TABLE: basic functionality -CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); - SELECT * FROM (VALUES @@ -360,7 +415,6 @@ FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -377,10 +431,7 @@ FROM js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, - jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$' + jsb2q jsonb PATH '$' OMIT QUOTES ) ) jt ON true; @@ -399,7 +450,6 @@ SELECT * FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -416,9 +466,6 @@ SELECT * FROM js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS ( a1 int, @@ -443,7 +490,6 @@ SELECT * FROM EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; DROP VIEW jsonb_table_view; -DROP DOMAIN jsonb_test_domain; -- JSON_TABLE: ON EMPTY/ON ERROR behavior SELECT * -- 2.17.1