Re: SQL/JSON path issues/questions

On 6/17/19 4:13 PM, Alexander Korotkov wrote:
> On Mon, Jun 17, 2019 at 8:40 PM Thom Brown <thom@linux.com> wrote:
>>>> "is unknown" suggests a boolean output, but the example shows an
>>>> output of "infinity".  While I understand what it does, this appears
>>>> inconsistent with all other "is..." functions (e.g. is_valid(lsn),
>>>> pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
>>>> pg_is_in_backup() etc.).
>>>
>>> It's the right behavior. Among them, only "infinity" gives
>>> "unknown' for the test (@ > 0). -1 gives false, 2 and 3 true.
>>
>> I still find it counter-intuitive.
> 
> It might be so.  But it's defined do in SQL Standard 2016.

IIUC, this comes about simply because the JSON data model for numeric
values does not have any infinity or NaN.

So the example given in our doc is sort of a trick example that does
double duty: it demonstrates that (@ > 0) is Unknown when @ is a string,
because numbers and strings are incomparable, and it *also* sort of
slyly reminds the reader that JSON numbers have no infinity, and
therefore "infinity" is nothing but a run-of-the-mill string.

But maybe it is just too brow-furrowingly clever to ask one example
to make both of those points. Maybe it would be clearer to use some
string other than "infinity" to make the first point:

[-1, 2, 7, "some string"] | $[*] ? ((@ > 0) is unknown) | "some string"

... and then if the reminder about infinity is worth making, repeat
the example:

[-1, 2, 7, "infinity"] | $[*] ? ((@ > 0) is unknown) | "infinity"

with a note that it's a trick example as a reminder that JSON numbers
don't have infinity or NaN and so it is no different from any other
string.

Regards,
-Chap