Skip to content

Commit 74c9669

Browse files
amitlanamitlan
committed
SQL/JSON: Some fixes to JsonBehavior expression casting
1. Remove the special case handling when casting the JsonBehavior expressions to types with typmod, like 86d3398 did for the casting of SQL/JSON constructor functions. 2. Fix casting for fixed-length character and bit string types by using assignment-level casts. This is again similar to what 86d3398 did, but for ON ERROR / EMPTY expressions. 3. Use runtime coercion for the boolean ON ERROR constants so that using fixed-length character string types, for example, for an EXISTS column doesn't cause a "value too long for type character(n)" when the parser tries to coerce the default ON ERROR value "false" to that type, that is, even when clause is not specified. 4. Simplify the conditions of when to use runtime coercion vs creating the cast expression in the parser itself. jsonb-valued expressions are now always coerced at runtime and boolean expressions too if the target type is a string type for the reasons mentioned above. Tests are taken from a patch that Jian He posted. Reported-by: Jian He <jian.universality@gmail.com> Author: Jian He <jian.universality@gmail.com> Author: Amit Langote <amitlangote09@gmail.com> Discussion: https://postgr.es/m/CACJufxEo4sUjKCYtda0_qt9tazqqKPmF1cqhW9KBOUeJFqQd2g@mail.gmail.com Backpatch-through: 17
1 parent 8240401 commit 74c9669

File tree

5 files changed

+164
-52
lines changed

5 files changed

+164
-52
lines changed

‎src/backend/parser/parse_expr.c

Lines changed: 70 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -4685,51 +4685,91 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
46854685
if (expr == NULL && btype != JSON_BEHAVIOR_ERROR)
46864686
expr = GetJsonBehaviorConst(btype, location);
46874687

4688-
if (expr)
4688+
/*
4689+
* Try to coerce the expression if needed.
4690+
*
4691+
* Use runtime coercion using json_populate_type() if the expression is
4692+
* NULL, jsonb-valued, or boolean-valued (unless the target type is
4693+
* integer or domain over integer, in which case use the
4694+
* boolean-to-integer cast function).
4695+
*
4696+
* For other non-NULL expressions, try to find a cast and error out if one
4697+
* is not found.
4698+
*/
4699+
if (expr && exprType(expr) != returning->typid)
46894700
{
4690-
Node *coerced_expr = expr;
46914701
bool isnull = (IsA(expr, Const) && ((Const *) expr)->constisnull);
46924702

4693-
/*
4694-
* Coerce NULLs and "internal" (that is, not specified by the user)
4695-
* jsonb-valued expressions at runtime using json_populate_type().
4696-
*
4697-
* For other (user-specified) non-NULL values, try to find a cast and
4698-
* error out if one is not found.
4699-
*/
47004703
if (isnull ||
4701-
(exprType(expr) == JSONBOID &&
4702-
btype == default_behavior))
4704+
exprType(expr) == JSONBOID ||
4705+
(exprType(expr) == BOOLOID &&
4706+
getBaseType(returning->typid) != INT4OID))
4707+
{
47034708
coerce_at_runtime = true;
4709+
4710+
/*
4711+
* json_populate_type() expects to be passed a jsonb value, so gin
4712+
* up a Const containing the appropriate boolean value represented
4713+
* as jsonb, discarding the original Const containing a plain
4714+
* boolean.
4715+
*/
4716+
if (exprType(expr) == BOOLOID)
4717+
{
4718+
char *val = btype == JSON_BEHAVIOR_TRUE ? "true" : "false";
4719+
4720+
expr = (Node *) makeConst(JSONBOID, -1, InvalidOid, -1,
4721+
DirectFunctionCall1(jsonb_in,
4722+
CStringGetDatum(val)),
4723+
false, false);
4724+
}
4725+
}
47044726
else
47054727
{
4706-
int32 baseTypmod = returning->typmod;
4728+
Node *coerced_expr;
4729+
char typcategory = TypeCategory(returning->typid);
47074730

4708-
if (get_typtype(returning->typid) == TYPTYPE_DOMAIN)
4709-
(void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
4710-
4711-
if (baseTypmod > 0)
4712-
expr = coerce_to_specific_type(pstate, expr, TEXTOID,
4713-
"JSON_FUNCTION()");
4731+
/*
4732+
* Use an assignment cast if coercing to a string type so that
4733+
* build_coercion_expression() assumes implicit coercion when
4734+
* coercing the typmod, so that inputs exceeding length cause an
4735+
* error instead of silent truncation.
4736+
*/
47144737
coerced_expr =
47154738
coerce_to_target_type(pstate, expr, exprType(expr),
4716-
returning->typid, baseTypmod,
4717-
baseTypmod > 0 ? COERCION_IMPLICIT :
4739+
returning->typid, returning->typmod,
4740+
(typcategory == TYPCATEGORY_STRING ||
4741+
typcategory == TYPCATEGORY_BITSTRING) ?
4742+
COERCION_ASSIGNMENT :
47184743
COERCION_EXPLICIT,
4719-
baseTypmod > 0 ? COERCE_IMPLICIT_CAST :
47204744
COERCE_EXPLICIT_CAST,
47214745
exprLocation((Node *) behavior));
4722-
}
47234746

4724-
if (coerced_expr == NULL)
4725-
ereport(ERROR,
4726-
errcode(ERRCODE_CANNOT_COERCE),
4727-
errmsg("cannot cast behavior expression of type %s to %s",
4728-
format_type_be(exprType(expr)),
4729-
format_type_be(returning->typid)),
4730-
parser_errposition(pstate, exprLocation(expr)));
4731-
else
4747+
if (coerced_expr == NULL)
4748+
{
4749+
/*
4750+
* Provide a HINT if the expression comes from a DEFAULT
4751+
* clause.
4752+
*/
4753+
if (btype == JSON_BEHAVIOR_DEFAULT)
4754+
ereport(ERROR,
4755+
errcode(ERRCODE_CANNOT_COERCE),
4756+
errmsg("cannot cast behavior expression of type %s to %s",
4757+
format_type_be(exprType(expr)),
4758+
format_type_be(returning->typid)),
4759+
errhint("You will need to explicitly cast the expression to type %s.",
4760+
format_type_be(returning->typid)),
4761+
parser_errposition(pstate, exprLocation(expr)));
4762+
else
4763+
ereport(ERROR,
4764+
errcode(ERRCODE_CANNOT_COERCE),
4765+
errmsg("cannot cast behavior expression of type %s to %s",
4766+
format_type_be(exprType(expr)),
4767+
format_type_be(returning->typid)),
4768+
parser_errposition(pstate, exprLocation(expr)));
4769+
}
4770+
47324771
expr = coerced_expr;
4772+
}
47334773
}
47344774

47354775
if (behavior)

‎src/test/regress/expected/sqljson_jsontable.out

Lines changed: 23 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -556,21 +556,38 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
556556
(1 row)
557557

558558
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
559-
ERROR: cannot cast behavior expression of type boolean to smallint
559+
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
560+
DETAIL: invalid input syntax for type smallint: "false"
560561
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
561-
ERROR: cannot cast behavior expression of type boolean to bigint
562+
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
563+
DETAIL: invalid input syntax for type bigint: "false"
562564
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
563-
ERROR: cannot cast behavior expression of type boolean to real
564-
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a'));
565+
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
566+
DETAIL: invalid input syntax for type real: "false"
567+
-- Default FALSE (ON ERROR) doesn't fit char(3)
568+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
569+
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
570+
DETAIL: value too long for type character(3)
571+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a' ERROR ON ERROR));
572+
ERROR: value too long for type character(3)
573+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' ERROR ON ERROR));
565574
a
566575
-------
567576
false
568577
(1 row)
569578

570579
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
571-
ERROR: cannot cast behavior expression of type boolean to json
580+
a
581+
-------
582+
false
583+
(1 row)
584+
572585
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
573-
ERROR: cannot cast behavior expression of type boolean to jsonb
586+
a
587+
-------
588+
false
589+
(1 row)
590+
574591
-- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
575592
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
576593
item

‎src/test/regress/expected/sqljson_queryfuncs.out

Lines changed: 52 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -662,7 +662,11 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bb
662662
(1 row)
663663

664664
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bb"'::jsonb ON EMPTY);
665-
ERROR: value too long for type character(2)
665+
json_query
666+
------------
667+
bb
668+
(1 row)
669+
666670
-- OMIT QUOTES behavior should not be specified when WITH WRAPPER used:
667671
-- Should fail
668672
SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
@@ -865,13 +869,17 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON);
865869
(1 row)
866870

867871
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR);
868-
ERROR: cannot cast behavior expression of type jsonb to bytea
869-
LINE 1: ... JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJE...
870-
^
872+
json_query
873+
------------
874+
\x7b7d
875+
(1 row)
876+
871877
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
872-
ERROR: cannot cast behavior expression of type jsonb to bytea
873-
LINE 1: ...jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJE...
874-
^
878+
json_query
879+
------------
880+
\x7b7d
881+
(1 row)
882+
875883
SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR);
876884
json_query
877885
------------
@@ -885,13 +893,11 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR);
885893
(1 row)
886894

887895
SELECT JSON_QUERY(jsonb '[3,4]', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR);
888-
ERROR: cannot cast behavior expression of type jsonb to bigint[]
889-
LINE 1: ...ON_QUERY(jsonb '[3,4]', '$[*]' RETURNING bigint[] EMPTY OBJE...
890-
^
896+
ERROR: could not coerce ON ERROR expression (EMPTY OBJECT) to the RETURNING type
897+
DETAIL: expected JSON array
891898
SELECT JSON_QUERY(jsonb '"[3,4]"', '$[*]' RETURNING bigint[] EMPTY OBJECT ON ERROR);
892-
ERROR: cannot cast behavior expression of type jsonb to bigint[]
893-
LINE 1: ..._QUERY(jsonb '"[3,4]"', '$[*]' RETURNING bigint[] EMPTY OBJE...
894-
^
899+
ERROR: could not coerce ON ERROR expression (EMPTY OBJECT) to the RETURNING type
900+
DETAIL: expected JSON array
895901
-- Coercion fails with quotes on
896902
SELECT JSON_QUERY(jsonb '"123.1"', '$' RETURNING int2 error on error);
897903
ERROR: invalid input syntax for type smallint: ""123.1""
@@ -1379,7 +1385,8 @@ ERROR: invalid ON ERROR behavior
13791385
LINE 1: SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);
13801386
^
13811387
DETAIL: Only ERROR, NULL, EMPTY [ ARRAY ], EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for JSON_QUERY().
1382-
-- Test implicit coercion domain over fixed-legth type specified in RETURNING
1388+
-- Test implicit coercion to a domain over fixed-length type specified in
1389+
-- RETURNING
13831390
CREATE DOMAIN queryfuncs_char2 AS char(2);
13841391
CREATE DOMAIN queryfuncs_char2_chk AS char(2) CHECK (VALUE NOT IN ('12'));
13851392
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
14151422
(1 row)
14161423

14171424
DROP DOMAIN queryfuncs_char2, queryfuncs_char2_chk;
1425+
-- Test coercion to domain over another fixed-length type of the ON ERROR /
1426+
-- EMPTY expressions. Ask user to cast the DEFAULT expression explicitly if
1427+
-- automatic casting cannot be done, for example, from int to bit(2).
1428+
CREATE DOMAIN queryfuncs_d_varbit3 AS varbit(3) CHECK (VALUE <> '01');
1429+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '111111' ON ERROR);
1430+
ERROR: bit string too long for type bit varying(3)
1431+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '010' ON ERROR);
1432+
json_value
1433+
------------
1434+
010
1435+
(1 row)
1436+
1437+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '01' ON ERROR);
1438+
ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type
1439+
DETAIL: value for domain queryfuncs_d_varbit3 violates check constraint "queryfuncs_d_varbit3_check"
1440+
SELECT JSON_VALUE(jsonb '"111"', '$' RETURNING bit(2) ERROR ON ERROR);
1441+
ERROR: bit string length 3 does not match type bit(2)
1442+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1 ON ERROR);
1443+
ERROR: cannot cast behavior expression of type integer to bit
1444+
LINE 1: ...VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1 ON ERROR...
1445+
^
1446+
HINT: You will need to explicitly cast the expression to type bit.
1447+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1::bit(3) ON ERROR);
1448+
json_value
1449+
------------
1450+
001
1451+
(1 row)
1452+
1453+
SELECT JSON_VALUE(jsonb '"111"', '$.a' RETURNING bit(3) DEFAULT '1111' ON EMPTY);
1454+
ERROR: bit string length 4 does not match type bit(3)
1455+
DROP DOMAIN queryfuncs_d_varbit3;

‎src/test/regress/sql/sqljson_jsontable.sql

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -266,7 +266,10 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
266266
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
267267
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
268268
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
269-
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a'));
269+
-- Default FALSE (ON ERROR) doesn't fit char(3)
270+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
271+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a' ERROR ON ERROR));
272+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' ERROR ON ERROR));
270273
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
271274
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
272275

‎src/test/regress/sql/sqljson_queryfuncs.sql

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -472,7 +472,8 @@ SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
472472
SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR);
473473
SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);
474474

475-
-- Test implicit coercion domain over fixed-legth type specified in RETURNING
475+
-- Test implicit coercion to a domain over fixed-length type specified in
476+
-- RETURNING
476477
CREATE DOMAIN queryfuncs_char2 AS char(2);
477478
CREATE DOMAIN queryfuncs_char2_chk AS char(2) CHECK (VALUE NOT IN ('12'));
478479
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
484485
SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk ERROR ON ERROR);
485486
SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT 1 ON ERROR);
486487
DROP DOMAIN queryfuncs_char2, queryfuncs_char2_chk;
488+
489+
-- Test coercion to domain over another fixed-length type of the ON ERROR /
490+
-- EMPTY expressions. Ask user to cast the DEFAULT expression explicitly if
491+
-- automatic casting cannot be done, for example, from int to bit(2).
492+
CREATE DOMAIN queryfuncs_d_varbit3 AS varbit(3) CHECK (VALUE <> '01');
493+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '111111' ON ERROR);
494+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '010' ON ERROR);
495+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING queryfuncs_d_varbit3 DEFAULT '01' ON ERROR);
496+
SELECT JSON_VALUE(jsonb '"111"', '$' RETURNING bit(2) ERROR ON ERROR);
497+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1 ON ERROR);
498+
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 1::bit(3) ON ERROR);
499+
SELECT JSON_VALUE(jsonb '"111"', '$.a' RETURNING bit(3) DEFAULT '1111' ON EMPTY);
500+
DROP DOMAIN queryfuncs_d_varbit3;

0 commit comments

Comments
 (0)