Re: remaining sql/json patches
От | jian he |
---|---|
Тема | Re: remaining sql/json patches |
Дата | |
Msg-id | CACJufxEzz6vRftewvefRFd7ADWAFnzXw848sdPq+9G5JvCPjcA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: remaining sql/json patches (Peter Smith <smithpb2250@gmail.com>) |
Список | pgsql-hackers |
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
Вложения
В списке pgsql-hackers по дате отправления: