diff options
| -rw-r--r-- | src/backend/parser/parse_expr.c | 100 | ||||
| -rw-r--r-- | src/test/regress/expected/sqljson_jsontable.out | 29 | ||||
| -rw-r--r-- | src/test/regress/expected/sqljson_queryfuncs.out | 66 | ||||
| -rw-r--r-- | src/test/regress/sql/sqljson_jsontable.sql | 5 | ||||
| -rw-r--r-- | src/test/regress/sql/sqljson_queryfuncs.sql | 16 |
5 files changed, 164 insertions, 52 deletions
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 8577f278065..d2db69a3f90 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4685,51 +4685,91 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior, if (expr == NULL && btype != JSON_BEHAVIOR_ERROR) expr = GetJsonBehaviorConst(btype, location); - if (expr) + /* + * Try to coerce the expression if needed. + * + * Use runtime coercion using json_populate_type() if the expression is + * NULL, jsonb-valued, or boolean-valued (unless the target type is + * integer or domain over integer, in which case use the + * boolean-to-integer cast function). + * + * For other non-NULL expressions, try to find a cast and error out if one + * is not found. + */ + if (expr && exprType(expr) != returning->typid) { - Node *coerced_expr = expr; bool isnull = (IsA(expr, Const) && ((Const *) expr)->constisnull); - /* - * Coerce NULLs and "internal" (that is, not specified by the user) - * jsonb-valued expressions at runtime using json_populate_type(). - * - * For other (user-specified) non-NULL values, try to find a cast and - * error out if one is not found. - */ if (isnull || - (exprType(expr) == JSONBOID && - btype == default_behavior)) + exprType(expr) == JSONBOID || + (exprType(expr) == BOOLOID && + getBaseType(returning->typid) != INT4OID)) + { coerce_at_runtime = true; + + /* + * json_populate_type() expects to be passed a jsonb value, so gin + * up a Const containing the appropriate boolean value represented + * as jsonb, discarding the original Const containing a plain + * boolean. + */ + if (exprType(expr) == BOOLOID) + { + char *val = btype == JSON_BEHAVIOR_TRUE ? "true" : "false"; + + expr = (Node *) makeConst(JSONBOID, -1, InvalidOid, -1, + DirectFunctionCall1(jsonb_in, + CStringGetDatum(val)), + false, false); + } + } else { - int32 baseTypmod = returning->typmod; + Node *coerced_expr; + char typcategory = TypeCategory(returning->typid); - if (get_typtype(returning->typid) == TYPTYPE_DOMAIN) - (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod); - - if (baseTypmod > 0) - expr = coerce_to_specific_type(pstate, expr, TEXTOID, - "JSON_FUNCTION()"); + /* + * Use an assignment cast if coercing to a string type so that + * build_coercion_expression() assumes implicit coercion when + * coercing the typmod, so that inputs exceeding length cause an + * error instead of silent truncation. + */ coerced_expr = coerce_to_target_type(pstate, expr, exprType(expr), - returning->typid, baseTypmod, - baseTypmod > 0 ? COERCION_IMPLICIT : + returning->typid, returning->typmod, + (typcategory == TYPCATEGORY_STRING || + typcategory == TYPCATEGORY_BITSTRING) ? + COERCION_ASSIGNMENT : COERCION_EXPLICIT, - baseTypmod > 0 ? COERCE_IMPLICIT_CAST : COERCE_EXPLICIT_CAST, exprLocation((Node *) behavior)); - } - if (coerced_expr == NULL) - ereport(ERROR, - errcode(ERRCODE_CANNOT_COERCE), - errmsg("cannot cast behavior expression of type %s to %s", - format_type_be(exprType(expr)), - format_type_be(returning->typid)), - parser_errposition(pstate, exprLocation(expr))); - else + if (coerced_expr == NULL) + { + /* + * Provide a HINT if the expression comes from a DEFAULT + * clause. + */ + if (btype == JSON_BEHAVIOR_DEFAULT) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast behavior expression of type %s to %s", + format_type_be(exprType(expr)), + format_type_be(returning->typid)), + errhint("You will need to explicitly cast the expression to type %s.", + format_type_be(returning->typid)), + parser_errposition(pstate, exprLocation(expr))); + else + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast behavior expression of type %s to %s", + format_type_be(exprType(expr)), + format_type_be(returning->typid)), + parser_errposition(pstate, exprLocation(expr))); + } + expr = coerced_expr; + } } if (behavior) diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index fcad9cc0289..24892d5e18a 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -556,21 +556,38 @@ 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 behavior expression of type boolean to smallint +ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type +DETAIL: invalid input syntax for type smallint: "false" SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a')); -ERROR: cannot cast behavior expression of type boolean to bigint +ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type +DETAIL: invalid input syntax for type bigint: "false" SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); -ERROR: cannot cast behavior expression of type boolean to real -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a')); +ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type +DETAIL: invalid input syntax for type real: "false" +-- Default FALSE (ON ERROR) doesn't fit char(3) +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')); +ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type +DETAIL: value too long for type character(3) +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a' ERROR ON ERROR)); +ERROR: value too long for type character(3) +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' ERROR ON ERROR)); a ------- false (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); -ERROR: cannot cast behavior expression of type boolean to json + a +------- + false +(1 row) + SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a')); -ERROR: cannot cast behavior expression of type boolean to jsonb + a +------- + false +(1 row) + -- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING)); item diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out index bdadf4b788e..52b0b029ff7 100644 --- a/src/test/regress/expected/sqljson_queryfuncs.out +++ b/src/test/regress/expected/sqljson_queryfuncs.out @@ -662,7 +662,11 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bb (1 row) SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bb"'::jsonb ON EMPTY); -ERROR: value too long for type character(2) + json_query +------------ + bb +(1 row) + -- OMIT QUOTES behavior should not be specified when WITH WRAPPER used: -- Should fail SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES); @@ -865,13 +869,17 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON); (1 row) SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR); -ERROR: cannot cast behavior expression of type jsonb to bytea -LINE 1: ... JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJE... - ^ + json_query +------------ + \x7b7d +(1 row) + SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR); -ERROR: cannot cast behavior expression of type jsonb to bytea -LINE 1: ...jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJE... - ^ + json_query +------------ + \x7b7d +(1 row) + SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR); json_query ------------ @@ -885,13 +893,11 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR); (1 row) SELECT JSON_QUERY(jsonb '[3,4]', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR); -ERROR: cannot cast behavior expression of type jsonb to bigint[] -LINE 1: ...ON_QUERY(jsonb '[3,4]', '$[*]' RETURNING bigint[] EMPTY OBJE... - ^ +ERROR: could not coerce ON ERROR expression (EMPTY OBJECT) to the RETURNING type +DETAIL: expected JSON array SELECT JSON_QUERY(jsonb '"[3,4]"', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR); -ERROR: cannot cast behavior expression of type jsonb to bigint[] -LINE 1: ..._QUERY(jsonb '"[3,4]"', '$[*]' RETURNING bigint[] EMPTY OBJE... - ^ +ERROR: could not coerce ON ERROR expression (EMPTY OBJECT) to the RETURNING type +DETAIL: expected JSON array -- Coercion fails with quotes on SELECT JSON_QUERY(jsonb '"123.1"', '$' RETURNING int2 error on error); ERROR: invalid input syntax for type smallint: ""123.1"" @@ -1379,7 +1385,8 @@ ERROR: invalid ON ERROR behavior LINE 1: SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); ^ DETAIL: Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for JSON_QUERY(). --- Test implicit coercion domain over fixed-legth type specified in RETURNING +-- Test implicit coercion to a domain over fixed-length type specified in +-- RETURNING CREATE DOMAIN queryfuncs_char2 AS char(2); CREATE DOMAIN queryfuncs_char2_chk AS char(2) CHECK (VALUE NOT IN ('12')); SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); @@ -1415,3 +1422,34 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT 1 ON E (1 row) DROP DOMAIN queryfuncs_char2, queryfuncs_char2_chk; +-- Test coercion to domain over another fixed-length type of the ON ERROR / +-- EMPTY expressions. Ask user to cast the DEFAULT expression explicitly if +-- automatic casting cannot be done, for example, from int to bit(2). +CREATE DOMAIN queryfuncs_d_varbit3 AS varbit(3) CHECK (VALUE <> '01'); +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '111111' ON ERROR); +ERROR: bit string too long for type bit varying(3) +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '010' ON ERROR); + json_value +------------ + 010 +(1 row) + +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '01' ON ERROR); +ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type +DETAIL: value for domain queryfuncs_d_varbit3 violates check constraint "queryfuncs_d_varbit3_check" +SELECT JSON_VALUE(jsonb '"111"', '$' RETURNING bit(2) ERROR ON ERROR); +ERROR: bit string length 3 does not match type bit(2) +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1 ON ERROR); +ERROR: cannot cast behavior expression of type integer to bit +LINE 1: ...VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1 ON ERROR... + ^ +HINT: You will need to explicitly cast the expression to type bit. +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1::bit(3) ON ERROR); + json_value +------------ + 001 +(1 row) + +SELECT JSON_VALUE(jsonb '"111"', '$.a' RETURNING bit(3) DEFAULT '1111' ON EMPTY); +ERROR: bit string length 4 does not match type bit(3) +DROP DOMAIN queryfuncs_d_varbit3; diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 4594e5b0138..dc509bed8af 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -266,7 +266,10 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a')); +-- Default FALSE (ON ERROR) doesn't fit char(3) +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a' ERROR ON ERROR)); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' ERROR ON ERROR)); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a')); diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql index e9005d3d4eb..21ff7787a28 100644 --- a/src/test/regress/sql/sqljson_queryfuncs.sql +++ b/src/test/regress/sql/sqljson_queryfuncs.sql @@ -472,7 +472,8 @@ SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR); SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR); SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); --- Test implicit coercion domain over fixed-legth type specified in RETURNING +-- Test implicit coercion to a domain over fixed-length type specified in +-- RETURNING CREATE DOMAIN queryfuncs_char2 AS char(2); CREATE DOMAIN queryfuncs_char2_chk AS char(2) CHECK (VALUE NOT IN ('12')); SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); @@ -484,3 +485,16 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 DEFAULT 1 ON ERROR SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk ERROR ON ERROR); SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT 1 ON ERROR); DROP DOMAIN queryfuncs_char2, queryfuncs_char2_chk; + +-- Test coercion to domain over another fixed-length type of the ON ERROR / +-- EMPTY expressions. Ask user to cast the DEFAULT expression explicitly if +-- automatic casting cannot be done, for example, from int to bit(2). +CREATE DOMAIN queryfuncs_d_varbit3 AS varbit(3) CHECK (VALUE <> '01'); +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '111111' ON ERROR); +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '010' ON ERROR); +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '01' ON ERROR); +SELECT JSON_VALUE(jsonb '"111"', '$' RETURNING bit(2) ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1 ON ERROR); +SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1::bit(3) ON ERROR); +SELECT JSON_VALUE(jsonb '"111"', '$.a' RETURNING bit(3) DEFAULT '1111' ON EMPTY); +DROP DOMAIN queryfuncs_d_varbit3; |