I found two main issues regarding cocece SQL/JSON function output to other data types. * returning typmod influence the returning result of JSON_VALUE | JSON_QUERY. * JSON_VALUE | JSON_QUERY handles returning type domains allowing null and not allowing null inconsistencies.
in ExecInitJsonExprCoercion, there is IsA(coercion,JsonCoercion) or
not difference.
for the returning of (JSON_VALUE | JSON_QUERY),
"coercion" is a JsonCoercion or not is set in coerceJsonFuncExprOutput.
this influence returning type with typmod is not -1.
if set "coercion" as JsonCoercion Node then it may call the
InputFunctionCallSafe to do the coercion.
If not, it may call ExecInitFunc related code which is wrapped in
ExecEvalCoerceViaIOSafe.
for example:
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
will ExecInitFunc, will init function bpchar(character, integer,
boolean). it will set the third argument to true.
so it will initiate related instructions like: `select
bpchar('[,2]',7,true); ` which in the end will make the result be
`[,2`
However, InputFunctionCallSafe cannot handle that.
simple demo:
create table t(a char(3));
--fail.
INSERT INTO t values ('test');
--ok
select 'test'::char(3);
however current ExecEvalCoerceViaIOSafe cannot handle omit quotes.
even if I made the changes, still not bullet-proof.
for example:
create domain char3_domain_not_null as char(3) NOT NULL;
create domain hello as text NOT NULL check (value = 'hello');
create domain int42 as int check (value = 42);
CREATE TYPE comp_domain_with_typmod AS (a char3_domain_not_null, b int42);
SELECT JSON_VALUE(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning
comp_domain_with_typmod);
will return NULL
however
SELECT JSON_VALUE(jsonb'{"rec": "abcd"}', '$.rec' returning
char3_domain_not_null);
will return `abc`.
I made the modification, you can see the difference.
attached is test_coerce.sql is the test file.
test_coerce_only_v35.out is the test output of only applying v35 0001
to 0007 plus my previous changes[0].
test_coerce_v35_plus_change.out is the test output of applying to v35
0001 to 0007 plus changes (attachment) and previous changes[0].
[0]
https://www.postgresql.org/message-id/CACJufxHo1VVk_0th3AsFxqdMgjaUDz6s0F7%2Bj9rYA3d%3DURw97A%40mail.gmail.com
test_coerce.sql
Description: application/sql
test_coerce_only_v35.out
Description: Binary data
test_coerce_v35_plus_change.out
Description: Binary data
v1-0001-make-JSON_QUERY-JSON_VALUE-returning-type-with.no-cfbot
Description: Binary data
