From 8db33b187c1a032d2080b557327b8d7bc2cf2e5d Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Mon, 15 Aug 2022 20:18:11 +0300 Subject: [PATCH v7 2/4] Fix returning of json[b] domains in JSON_VALUE() --- src/backend/parser/parse_expr.c | 52 ++++-- src/include/executor/execExpr.h | 3 + src/test/regress/expected/jsonb_sqljson.out | 181 +++++++++++++++++++- src/test/regress/sql/jsonb_sqljson.sql | 63 ++++++- 4 files changed, 287 insertions(+), 12 deletions(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 3b82c0f3328..aff6fc1a1c4 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4212,12 +4212,29 @@ coerceJsonExpr(ParseState *pstate, Node *expr, return coercion; } +/* Is it a json/jsonb type or its domain? */ +static bool +isJsonType(Oid typid) +{ + Oid basetypid; + + if (typid == JSONOID || typid == JSONBOID) + return true; + + basetypid = getBaseType(typid); + + if (basetypid == JSONOID || basetypid == JSONBOID) + return true; + + return false; +} + /* * Transform a JSON output clause of JSON_VALUE and JSON_QUERY. */ static void transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, - JsonExpr *jsexpr) + JsonExpr *jsexpr, bool *returning_json) { Node *expr = jsexpr->formatted_expr; @@ -4234,11 +4251,19 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, { JsonReturning ret; - if (func->op == JSON_VALUE_OP && - jsexpr->returning->typid != JSONOID && - jsexpr->returning->typid != JSONBOID) + *returning_json = isJsonType(jsexpr->returning->typid); + + if (func->op == JSON_VALUE_OP && !*returning_json) { - /* Forced coercion via I/O for JSON_VALUE for non-JSON types */ + /* + * In JSON_VALUE result_coercion can only be used when + * returning JSON types and for NULL values (resulting + * item is JSON null or NULL behavior is used). + * So there is no need to find a cast from json[b] to + * output type when it is a non-JSON type, we only need to + * check domains for NOT NULL, what can be done using I/O + * coercion. + */ jsexpr->result_coercion = makeNode(JsonCoercion); jsexpr->result_coercion->ctype = JSON_COERCION_VIA_IO; return; @@ -4260,8 +4285,11 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, } } else + { assignDefaultJsonReturningType(jsexpr->formatted_expr, jsexpr->format, jsexpr->returning); + *returning_json = true; + } } /* @@ -4371,13 +4399,14 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) JsonExpr *jsexpr = transformJsonExprCommon(pstate, func); const char *func_name = NULL; Node *contextItemExpr = jsexpr->formatted_expr; + bool returning_json; switch (func->op) { case JSON_VALUE_OP: func_name = "JSON_VALUE"; - transformJsonFuncExprOutput(pstate, func, jsexpr); + transformJsonFuncExprOutput(pstate, func, jsexpr, &returning_json); jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT; jsexpr->returning->format->encoding = JS_ENC_DEFAULT; @@ -4390,19 +4419,22 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) coerceDefaultJsonExpr(pstate, jsexpr, jsexpr->on_error->default_expr); - if (jsexpr->returning->typid != JSONOID && - jsexpr->returning->typid != JSONBOID) + /* + * Don't initialize item coercions if returning one of JSON + * types or their domains. Coercion will be done using + * "result_coercion". + */ + if (!returning_json) jsexpr->coercions = initJsonItemCoercions(pstate, jsexpr->returning, exprType(contextItemExpr)); - break; case JSON_QUERY_OP: func_name = "JSON_QUERY"; - transformJsonFuncExprOutput(pstate, func, jsexpr); + transformJsonFuncExprOutput(pstate, func, jsexpr, &returning_json); jsexpr->on_empty->default_expr = coerceDefaultJsonExpr(pstate, jsexpr, diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index 7dd96b8d5ba..51e8d93178e 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -789,6 +789,9 @@ typedef struct JsonExprState /* * States for coercion of SQL/JSON items produced in JSON_VALUE * directly to the output type. + * + * `item_coercions == NULL` means output type is json[b] or its + * domain and we use `result_coercion`. */ struct JsonItemsCoercionStates { diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index ef496110af3..ee5e597909c 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -349,14 +349,193 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; 03-01-2017 (1 row) --- Test NULL checks execution in domain types +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); + json_value +------------ + "1" +(1 row) + +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); + json_value +------------ + "1" +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null); + json_value +------------ + 1 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null); + json_value +------------ + 1 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null); + json_value +------------ + true +(1 row) + +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null); + json_value +------------ + true +(1 row) + +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR); + json_value +------------ + 2 +(1 row) + SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); ERROR: domain sqljsonb_int_not_null does not allow null values SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null); +ERROR: domain sqljsonb_json_not_null does not allow null values +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR); +ERROR: value for domain sqljsonb_json_not_null violates check constraint "sqljsonb_json_not_null_check" +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: domain sqljsonb_jsonb_not_null does not allow null values +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR); +ERROR: value for domain sqljsonb_jsonb_not_null violates check constraint "sqljsonb_jsonb_not_null_check" +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + json_value +------------ + 2 +(1 row) + +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); json_value ------------ diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index fff25374808..d70104b70ef 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -82,12 +82,73 @@ SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234; SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; --- Test NULL checks execution in domain types +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); + +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null); + +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR); SELECT JSON_VALUE(jsonb '{}', '$'); -- 2.25.1