From: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Subject: Re: jsonpath versus NaN
To: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>
Cc: Oleg Bartunov <obartunov@postgrespro.ru>,
 Alexander Korotkov <a.korotkov@postgrespro.ru>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <203949.1591879542@sss.pgh.pa.us>
 <CAPpHfdtLr1MkBr0Sgzu+HO0fkEP3W11SmLtOoBwWox8MiQdpmA@mail.gmail.com>
 <355114.1591902003@sss.pgh.pa.us>
 <CAPpHfdtizzX++0DUVL0_CCJjp6Rd-NT9rmqe0PvL7-t62Eq0oA@mail.gmail.com>
 <1446772.1592407984@sss.pgh.pa.us>
 <CAF4Au4yPfxBO7HOryzoNpm_6y8c0gFHN=G_7wj6v5226HrKrKA@mail.gmail.com>
 <CA+TgmobNwqNf2=4+sXy5_Y1FpWcxYf7ybkt-dk1dQqSTkB_qGg@mail.gmail.com>
 <1553053.1592498154@sss.pgh.pa.us>
Autocrypt: addr=andrew.dunstan@2ndQuadrant.com; prefer-encrypt=mutual;
 keydata=
 mQENBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAG0L0FuZHJldyBEdW5zdGFuIDxhbmRyZXcuZHVuc3RhbkAybmRRdWFkcmFudC5jb20+iQFY
 BBMBAgBCAhsDBgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ
 /DuBAhkBBQJd1pSYBQkSzqMsAAoJEJn6f8tZ/DuBJmEIAIVFBIC3qGqh+YTPVTkfowwxpHz0
 tZSPjZ6GS/5y62kgoZrZOVXw+Rg2ceuMM3a+In/RRjTx4bembgYqRKgzXlDfiNBJeq5ioVuN
 H1faDKE2lm5a6QkRQ2JU6DKf60fqSJGP5LisCUr1V0wz6EnlO31ArrKtXCNB+yk0KDAS6o6I
 Ql4pkXpr+vq4iilwJmiJOqTBam0LmQE7ulYCCYU8Z6MkpiSl+dO7QSramcGyxauQRI3a7gcq
 dpo8Vrv3gFTQDeMYxk7cqZp08pIVH5XR0+LgBz4X84f59rA/VAKlC/r6CyEQk59rvDLxCMyP
 2buoj51Z25N3MVghRDGcsU6mT7e5AQ0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oK
 Waqf+wmW4tpDe/VVcxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7
 llV0xSSoFiieQBsrGvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8
 BoVrRFcdzlIY/NFNhFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C
 4R+M6w7o2PLGOjdZ+kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaY
 L++LAEOqrlvm2TvqQqZRtnEAEQEAAYkBJQQYAQIADwIbDAUCXdaUtAUJEs6jVQAKCRCZ+n/L
 Wfw7gQi5B/9sHbmRloZNCWWwZGm+O9iG/JGQxS0WWa4GaZqzqSTWOft2/gO0Bgag89r1djq4
 PFgSZtzHvKQpqYz/7HkPrWh1jpcS83i/uYY1uyHXUUtpdEmn4buNObEtD0YNaJkGdhASsECq
 6akw+u7WJYmAHs/SpR6ok6HqLw7JHDVc2h5dViLfTk0qrgDa7sq/LVMoJje5Ct/Ku3YaWAV6
 YSmzoPP5BmPVFqP11OMQkCqurIu9vTLXKFWX623vCmqRDHJ6e5GCDHbH8USU+4wJBfPymZkd
 cA7NGm/mmWeJiWBFIDyjzYxrjku8ty9TZJVsaRXnS+OFQzXz9Eu2wTpKdHij46uY
Message-ID: <94b73a6f-16e3-252c-44ec-973957f36bd0@2ndQuadrant.com>
Date: Thu, 18 Jun 2020 16:53:15 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.6.0
MIME-Version: 1.0
In-Reply-To: <1553053.1592498154@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk


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