we still have problem in transformJsonBehavior
currently transformJsonBehavior:
SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 010111 ON ERROR);
ERROR: cannot cast behavior expression of type text to bit
LINE 1: ...VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 010111 ON ...
here, 010111 will default to int4, so "cannot cast behavior expression
of type text to bit"
is wrong?
also int4/int8 can be explicitly cast to bit(3), in this case, it
should return 111.
Also, do we want to deal with bit data type's typmod like we did for
string type in transformJsonBehavior?
like:
SELECT JSON_VALUE(jsonb '"111"', '$' RETURNING bit(3) default '1111' on error);
should return error:
ERROR: bit string length 2 does not match type bit(3)
or success
The attached patch makes it return an error, similar to what we did
for the fixed length string type.
From d457e3efebaaf1882693f7c9e2afd49520bce2a8 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 18 Jul 2024 13:38:19 +0800
Subject: [PATCH v1 1/1] fix transformJsonBehavior coerce to different types.
make transformJsonBehavior behave the same as for fix length string type
and fix length bit string type.
make transformJsonBehavior coerce to fix length string type aware the length.
---
src/backend/parser/parse_expr.c | 63 ++++++++++++++-----
.../regress/expected/sqljson_queryfuncs.out | 29 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 11 ++++
3 files changed, 89 insertions(+), 14 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 8577f278..5aadff34 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4703,22 +4703,57 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
coerce_at_runtime = true;
else
{
- int32 baseTypmod = returning->typmod;
+ char typcategory;
+ bool typispreferred;
+ get_type_category_preferred(returning->typid, &typcategory, &typispreferred);
- if (get_typtype(returning->typid) == TYPTYPE_DOMAIN)
- (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+ if (typcategory == TYPCATEGORY_STRING)
+ coerced_expr = coerce_to_target_type(pstate, expr, exprType(expr),
+ returning->typid, returning->typmod,
+ COERCION_ASSIGNMENT,
+ COERCE_IMPLICIT_CAST,
+ location);
+ else if (typcategory == TYPCATEGORY_BITSTRING)
+ {
+ int32 baseTypmod = -1;
+ CoercionContext ccontext = COERCION_EXPLICIT;
+ CoercionForm cformat = COERCE_EXPLICIT_CAST;
- if (baseTypmod > 0)
- expr = coerce_to_specific_type(pstate, expr, TEXTOID,
- "JSON_FUNCTION()");
- coerced_expr =
- coerce_to_target_type(pstate, expr, exprType(expr),
- returning->typid, baseTypmod,
- baseTypmod > 0 ? COERCION_IMPLICIT :
- COERCION_EXPLICIT,
- baseTypmod > 0 ? COERCE_IMPLICIT_CAST :
- COERCE_EXPLICIT_CAST,
- exprLocation((Node *) behavior));
+ if (get_typtype(returning->typid) == TYPTYPE_DOMAIN)
+ (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+
+ /*
+ * '111'::bit(2); typmod aware should fail.
+ * '111'::bit(2); don't care typmod should ok.
+ * here typmod refers to domain's basetype typmod when type is domain.
+ * pg_cast only have explicit cast entry between bit and int4/int8,
+ * we need set it accordingly,
+ * so DEFAULT 21 ON ERROR, DEFAULT 11 ON ERROR should ok;
+ */
+ if(baseTypmod > 0 || returning->typmod > 0)
+ {
+ ccontext = COERCION_IMPLICIT;
+ cformat = COERCE_IMPLICIT_CAST;
+ }
+ if (exprType(expr) == INT4OID || exprType(expr) == INT8OID)
+ {
+ ccontext = COERCION_EXPLICIT;
+ cformat = COERCE_EXPLICIT_CAST;
+ }
+
+ coerced_expr =
+ coerce_to_target_type(pstate, expr, exprType(expr),
+ returning->typid, returning->typmod,
+ ccontext,
+ cformat,
+ exprLocation((Node *) behavior));
+ }
+ else
+ coerced_expr =
+ coerce_to_target_type(pstate, expr, exprType(expr),
+ returning->typid, returning->typmod,
+ COERCION_EXPLICIT, COERCE_EXPLICIT_CAST,
+ exprLocation((Node *) behavior));
}
if (coerced_expr == NULL)
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 074aedb2..62d8452d 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1405,3 +1405,32 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2_chk DEFAULT 1 ON E
(1 row)
DROP DOMAIN queryfuncs_char2, queryfuncs_char2_chk;
+-- Test implicit coercion over fixed-legth type specified in ON ERROR, ON EMPTY.
+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: 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::int8 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)
+SELECT JSON_VALUE(jsonb '1234', '$.a' RETURNING bit(3) DEFAULT 1::int4 ON EMPTY);
+ json_value
+------------
+ 001
+(1 row)
+
+DROP DOMAIN queryfuncs_d_varbit3;
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index be5593b3..f1402704 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -479,3 +479,14 @@ 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 implicit coercion over fixed-legth type specified in ON ERROR, ON EMPTY.
+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::int8 ON ERROR);
+SELECT JSON_VALUE(jsonb '"111"', '$.a' RETURNING bit(3) default '1111' on empty);
+SELECT JSON_VALUE(jsonb '1234', '$.a' RETURNING bit(3) DEFAULT 1::int4 ON EMPTY);
+DROP DOMAIN queryfuncs_d_varbit3;
\ No newline at end of file
--
2.34.1
