public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Amit Langote <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: remaining sql/json patches
Date: Mon, 22 Jan 2024 14:14:07 +0800
Message-ID: <CACJufxEzz6vRftewvefRFd7ADWAFnzXw848sdPq+9G5JvCPjcA@mail.gmail.com> (raw)
In-Reply-To: <CAHut+PsMu3tPU0croX9R_gcgE8Fz6RPbr4O9Ph8e_G6vSsDjWQ@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+HiwqEZP-ybbvTZ_2gKJD2p60hvDkL9-UsoKrzirZkwdV60Ng@mail.gmail.com>
<CACJufxFTRw_o-Fey-Km1uavuUAZagXFgQLf8DsMoT_qU4AU8hw@mail.gmail.com>
<CACJufxHo1VVk_0th3AsFxqdMgjaUDz6s0F7+j9rYA3d=URw97A@mail.gmail.com>
<CAHut+PsMu3tPU0croX9R_gcgE8Fz6RPbr4O9Ph8e_G6vSsDjWQ@mail.gmail.com>
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...
Attachments:
[application/sql] test_coerce.sql (3.9K, ../CACJufxEzz6vRftewvefRFd7ADWAFnzXw848sdPq+9G5JvCPjcA@mail.gmail.com/2-test_coerce.sql)
download
[application/octet-stream] test_coerce_only_v35.out (6.2K, ../CACJufxEzz6vRftewvefRFd7ADWAFnzXw848sdPq+9G5JvCPjcA@mail.gmail.com/3-test_coerce_only_v35.out)
download
[application/octet-stream] test_coerce_v35_plus_change.out (6.3K, ../CACJufxEzz6vRftewvefRFd7ADWAFnzXw848sdPq+9G5JvCPjcA@mail.gmail.com/4-test_coerce_v35_plus_change.out)
download
[application/octet-stream] v1-0001-make-JSON_QUERY-JSON_VALUE-returning-type-with.no-cfbot (11.6K, ../CACJufxEzz6vRftewvefRFd7ADWAFnzXw848sdPq+9G5JvCPjcA@mail.gmail.com/5-v1-0001-make-JSON_QUERY-JSON_VALUE-returning-type-with.no-cfbot)
download
view thread (27+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: remaining sql/json patches
In-Reply-To: <CACJufxEzz6vRftewvefRFd7ADWAFnzXw848sdPq+9G5JvCPjcA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox