Re: jsonpath versus NaN

On 6/18/20 12:35 PM, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, Jun 18, 2020 at 11:51 AM Oleg Bartunov <obartunov@postgrespro.ru> wrote:
>>> The problem is that we tried to find a trade-off  between standard and postgres
>>> implementation, for example, in postgres CAST  allows NaN and Inf, and SQL Standard
>>> requires .double should works as CAST.
>> It seems like the right thing is to implement the standard, not to
>> implement whatever PostgreSQL happens to do in other cases. I can't
>> help feeling like re-using the numeric data type for other things has
>> led to this confusion. I think that fails in other cases, too: like
>> what if you have a super-long integer that can't be represented as a
>> numeric? I bet jsonb will fail, or maybe it will convert it to a
>> string, but I don't see how it can do anything else.
> Actually, the JSON spec explicitly says that any number that doesn't fit
> in an IEEE double isn't portable [1].  So we're already very far above and
> beyond the spec's requirements by using numeric.  We don't need to improve
> on that.  But I concur with your point that just because PG does X in
> some other cases doesn't mean that we must do X in json or jsonpath.
>
>             regards, tom lane
>
> [1] https://tools.ietf.org/html/rfc7159#page-6
>
>    This specification allows implementations to set limits on the range
>    and precision of numbers accepted.  Since software that implements
>    IEEE 754-2008 binary64 (double precision) numbers [IEEE754] is
>    generally available and widely used, good interoperability can be
>    achieved by implementations that expect no more precision or range
>    than these provide, in the sense that implementations will
>    approximate JSON numbers within the expected precision.  A JSON
>    number such as 1E400 or 3.141592653589793238462643383279 may indicate
>    potential interoperability problems, since it suggests that the
>    software that created it expects receiving software to have greater
>    capabilities for numeric magnitude and precision than is widely
>    available.
>
>    Note that when such software is used, numbers that are integers and
>    are in the range [-(2**53)+1, (2**53)-1] are interoperable in the
>    sense that implementations will agree exactly on their numeric
>    values.
>


Just to complete the historical record, that standard wasn't published
at the time we created the JSON type, and the then existing standard
(rfc4627) contains no such statement. We felt it was important to be
able to represent any Postgres data value in as natural a manner as
possible given the constraints of JSON. rfc7159 was published just as we
were finalizing 9.4 with JSONB, although I'm not sure it made a heavy
impact on our consciousness. If it had I would still not have wanted to
impose any additional limitation on numerics. If you want portable
numbers cast the numeric to double before producing the JSON.

ISTR having a conversation about the extended use of jsonb in jsonpath a
while back, although I don't remember if that was on or off list. I know
it troubled me some.


cheers


andrew



-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services