From 847ee701bd3a6d222ab8f67923e308ee1a70a2f8 Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Tue, 30 Jul 2024 10:37:56 +0900 Subject: SQL/JSON: Some fixes to JsonBehavior expression casting 1. Remove the special case handling when casting the JsonBehavior expressions to types with typmod, like 86d33987 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 86d33987 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. New tests are from a patch that Jian He posted. Outputs of some existing tests change because the coercion now happens at runtime instead of at parse time. Reported-by: Jian He Author: Jian He Author: Amit Langote Discussion: https://postgr.es/m/CACJufxEo4sUjKCYtda0_qt9tazqqKPmF1cqhW9KBOUeJFqQd2g@mail.gmail.com Backpatch-through: 17 --- src/test/regress/sql/sqljson_queryfuncs.sql | 16 +++++++++++++++- 1 file changed, 15 insertions(+), 1 deletion(-) (limited to 'src/test/regress/sql/sqljson_queryfuncs.sql') 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; -- cgit v1.2.3