Re: SQL/JSON: functions

so 18. 1. 2020 v 18:46 odesílatel Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
napsal:

> Attached 41th version of the patches.
>
> Changes since previous version:
> * Enabled DEFAULT clause for ON ERROR/ON EMPTY behaviors in JSON_QUERY()
> * Added RETURNING clause to JSON_EXISTS() ("side effect" of implementation
> EXISTS PATH columns in JSON_TABLE)
> * ARRAY in EMPTY ARRAY ON ERROR clause is optional now for better Oracle
> compatibility
>
>
> On 17.01.2020 9:54, Pavel Stehule wrote:
>
> On 14. 11. 2019 v 17:46 Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> wrote:
>
>> Attached 40th version of the patches.
>>
>> I have added some documentation which has been simply copied from [1].
>>
>> Also, I have split patches more correctly, and extracted patch #2 with common
>> SQL/JSON clauses, that are used by both constructors and query functions.
>> Patches #3 and #4 are necessary only for constructors (patch #5).
>> Patches #5 and #6 are almost independent on patch #1, which is needed only for
>> query functions.
>>
>> So, patches #5, #6, #7 are independent now, but the code changes in them are
>> still conflicting. I can rebase #6 and #7 on top of #2, if it is necessary
>> for separate review/commit.
>>
>>
>> [1] https://www.postgresql.org/message-id/732208d3-56c3-25a4-8f08-3be1d54ad51b%40postgrespro.ru
>>
>> I tested cumulative patch - sent in json_table patch.
>
> I almost satisfied by quality of this patch. There is very good
> conformance with standard and with Oracle. Unfortunately MySQL in this part
> of JSON support is not compatible.
>
> I found one issue, when I tested some examples from Oracle.
>
> SELECT JSON_VALUE('{a:100}', '$.a' RETURNING int) AS value;
>
> then the result was null.
>
> But it is wrong, because it should to raise a exception, because this json
> is broken on Postgres (Postgres requires quoted attribute names)
>
> json_query has same problem
>
> postgres=# SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value;
> ┌───────┐
> │ value │
> ╞═══════╡
> │ ∅ │
> └───────┘
> (1 row)
>
> It should to check if input is correct json
>
> By the standard, it is implementation-defined whether JSON parsing errors
> should be caught by ON ERROR clause.
>
> SQL/JSON query functions use "JSON API common syntax" which is a combination
> of JSON context item and JSON path. It passes context item to JSON path
> engine with ALREADY PARSED flag set to False. ALREADY PARSED flag can enable
> special parsing rules.
>
> Corresponding quotes from the standard:
>
> 10.14 <JSON API common syntax>
> <JSON API common syntax> (
> Parameter: "JSON API COMMON SYNTAX"
> ) Returns: "STATUS" and "SQL/JSON SEQUENCE"
>
> General Rules:
> ...
> 3) General Rules of Subclause 9.39, "SQL/JSON path language: syntax and
> semantics", are applied with P as PATH SPECIFICATION, C as CONTEXT ITEM,
> False as ALREADY PARSED, and PC as PASSING CLAUSE; let ST be the STATUS
> and let SEQ be the SQL/JSON SEQUENCE returned from the application of
> those General Rules.
>
> 9.39 SQL/JSON path language: syntax and semantics
>
> "SQL/JSON path language: syntax and semantics" [General Rules] (
> Parameter: "PATH SPECIFICATION",
> Parameter: "CONTEXT ITEM",
> Parameter: "ALREADY PARSED",
> Parameter: "PASSING CLAUSE"
> ) Returns: "STATUS" and "SQL/JSON SEQUENCE"
>
> General Rules:
> ...
>
> 4) If ALREADY PARSED is False, then it is implementation-defined whether the
> following rules are applied:
> a) The General Rules of Subclause 9.36, "Parsing JSON text", are applied with
> JT as JSON TEXT, an implementation-defined <JSON key uniqueness constraint>
> as UNIQUENESS CONSTRAINT, and FO as FORMAT OPTION; let ST be the STATUS and
> let CISJI be the SQL/JSON ITEM returned from the application of those
> General Rules.
> b) If ST is not successful completion, then ST is returned as the STATUS of
> this application of these General Rules, and no further General Rules of
> this Subclause are applied.
>
>
> I decided to apply this rules, so the parsing errors are caught now by ON ERROR
> (NULL ON ERROR is by default).
>
> postgres=# SELECT JSON_VALUE('error', '$' ERROR ON ERROR);
> ERROR: invalid input syntax for type json
> DETAIL: Token "error" is invalid.
> CONTEXT: JSON data, line 1: error
>
> I'm not sure if it would be better to add an implicit cast to json type that
> will be executed before so that parsing errors can no longer be caught.
> But implicit casting can simplify a bit execution of SQL/JSON query functions.
>
>
> I have checked error handling in JSON parsing in Oracle 18c/19c, and it behaves
> like our current implementation. But Oracle seems to do JSON parsing on demand:
>
> Oracle19c> SELECT JSON_VALUE('{a:1 error, b:2}', '$.a' ERROR ON ERROR) FROM dual;
> 1
>
> Oracle19c> SELECT JSON_VALUE('{a:1 error, b:2}', '$.b' ERROR ON ERROR) FROM dual;
> ORA-40441: JSON syntax error
>
> Oracle19c> SELECT JSON_VALUE('{a:1 error, b:2}', '$.b') FROM dual;
> NULL
>
>
Everywhere I don't like default masking error. I think so can be very
confusing to get NULL (by default) instead a error of broken format.

I vote for check of input is correct JSON, and if it, then start
processing. Else to raise a error.

More - our JSON Parser is different than Oracle's JSON parser. And if
somebody will run Oracle's JSONs, then he get some result on Oracle. But on
Postgres, same JSON can be invalid, and he get NULL.

The raising some errors looks like only one safe variant.

Regards

Pavel

> --
> Nikita Glukhov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>