Re: Fix inconsistency in jsonpath .datetime() - Mailing list pgsql-hackers

On Sun, Sep 20, 2020 at 2:23 AM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote:
> The beta-tester of PG13 reported a inconsistency in our current jsonpath
> datetime() method implementation.  By the standard format strings in datetime()
> allows only characters "-./,':; " to be used as separators in format strings.
> But our to_json[b]() serializes timestamps into XSD format with "T" separator
> between date and time, so the serialized data cannot be parsed back by jsonpath
> and it looks inconsistent:
>
> =# SELECT to_jsonb('2020-09-19 23:45:06'::timestamp);
>        to_jsonb
> -----------------------
>  "2020-09-19T23:45:06"
> (1 row)
>
> =# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
>                            '$.datetime()');
> ERROR:  datetime format is not recognized: "2020-09-19T23:45:06"
> HINT:  Use a datetime template argument to specify the input data format.
>
> =# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
>                            '$.datetime("yyyy-mm-dd HH:MI:SS")');
> ERROR:  unmatched format separator " "
>
> =# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
>                            '$.datetime("yyyy-mm-dd\"T\"HH:MI:SS")');
> ERROR:  invalid datetime format separator: """
>
>
>
> Excerpt from SQL-2916 standard (5.3 <literal>, page 197):
>
> <unquoted timestamp string> ::=
>   <unquoted date string> <space> <unquoted time string>
>
> <unquoted time string> ::=
>   <time value> [ <time zone interval> ]
>
> <time zone interval> ::=
>   <sign> <hours value> <colon> <minutes value>
>
>
>
> Attached patch #2 tries to fix this problem by enabling escaped characters in
> standard mode.  I'm not sure is it better to enable the whole set of text
> separators or only the problematic "T" character, allow only quoted text
> separators or not.
>
> Patch #1 is a more simple fix (so it comes first) removing excess space between
> time and timezone fields in built-in format strings used for datetime type
> recognition.  (It seemed to work as expected with extra space in earlier
> version of the patch in which standard mode has not yet been introduced).

Jsonpath .datetime() was developed as an implementation of
corresponding parts of SQL Standard.  Patch #1 fixes inconsistency
between our implementation and Standard.  I'm going to backpatch it to
v13.

There is also inconsistency among to_json[b]() and jsonpath
.datetime().  In this case, I wouldn't say the problem is on the
jsonpath side.  to_json[b]() makes special exceptions for datetime
types and converts them not using standard output function, but using
javascript-compatible format (see f30015b6d7).  Luckily, our input
function for timestamp[tz] datatypes doesn't use strict format
parsing, so it can work with output of to_json[b]().  But according to
SQL Standard, jsonpath .datetime() implements strict format parsing,
so it can't work with output of to_json[b]().  So, I wouldn't say in
this case it's an inconsistency in the jsonpath .datetime() method.
But, given now it's not an appropriate time for redesigning
to_json[b](), we should probably improve jsonpath .datetime() method
to understand more formats.

So, patch #2 is probably acceptable, and even might be backpatched
v13.  One thing I don't particularly like is "In standard mode format
string characters are strictly matched or matched to spaces."
Instead, I would like to just strictly match characters and just add
more options to fmt_str[].

Other opinions?

------
Regards,
Alexander Korotkov