Subject: Re: jsonpath: Missing regex_like && starts with Errors?
To: "David E. Wheeler" <david@justatheory.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <9272D3A9-8805-4DE1-A95F-84EB210EC121@justatheory.com>
From: Chapman Flack <jcflack@acm.org>
Message-ID: <666CFC8E.1000108@acm.org>
Date: Fri, 14 Jun 2024 22:29:34 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: <9272D3A9-8805-4DE1-A95F-84EB210EC121@justatheory.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/666CFC8E.1000108%40acm.org>
Precedence: bulk

On 06/14/24 12:21, David E. Wheeler wrote:
> I noticed that neither `regex_like` nor `starts with`, the jsonpath ope=
rators, raise an error when the operand is not a string (or array of stri=
ngs):
>=20
> david=3D# select jsonb_path_query('true', '$ like_regex "^hi"');
>  jsonb_path_query=20
> ------------------
>  null
> (1 row)
>=20
> david=3D# select jsonb_path_query('{"x": "hi"}', '$ starts with "^hi"')=
;
>  jsonb_path_query=20
> ------------------
>  null
> (1 row)

To begin with, both of those path queries should have been rejected at
the parsing stage, just like the one David Johnson pointed out:

On 06/13/24 22:14, David G. Johnston wrote:
> On Thursday, June 13, 2024, Chapman Flack <jcflack@acm.org> wrote:
>> On 06/13/24 21:46, David G. Johnston wrote:
>>>>> david=3D# select jsonb_path_query('1', '$ >=3D 1');
>>>>
>>>> Good point. I can't either. No way I can see to parse that as
>>>> a <JSON path wff>.
>>>
>>> Whether we note it as non-standard or not is an open question then, b=
ut
>> it
>>> does work and opens up a documentation question.

All of these are <JSON path predicate> appearing where a <JSON path wff>
is needed, and that's not allowed in the standard. Strictly speaking, the
only place <JSON path predicate> can appear is within <JSON filter expres=
sion>.

So I should go look at our code to see what grammar we've implemented,
exactly. It is beginning to seem as if we have simply added
<JSON path predicate> as another choice for an expression, not restricted
to only appearing in a filter. If so, and we add documentation about how
we diverge from the standard, that's probably the way to say it.

On 06/13/24 22:14, David G. Johnston wrote:
> I don=E2=80=99t get why the outcome of a boolean producing operation is=
n=E2=80=99t just
> generally allowed to be produced

I understand; after all, what is a 'predicate' but another 'boolean
producing operation'? But the committee (at least in this edition) has
stuck us with this clear division in the grammar: there is no
<JSON path wff>, boolean as it may be, that can appear as a
<JSON path predicate>, and there is no <JSON path predicate> that
can appear outside of a filter and be treated as a boolean-valued
expression.

As for the error behavior of a <JSON path predicate> (which strictly,
again, can only appear inside a <JSON filter expression>), the standard
says what seems to be the same thing, in a couple different ways.

In 4.48.5 Overview of SQL/JSON path language, this is said: "The SQL/JSON
path language traps any errors that occur during the evaluation of a
<JSON filter expression>. Depending on the precise <JSON path predicate> =
...
the result may be Unknown, True, or False, ...".

Later in 9.46's General Rules where the specific semantics of the
various predicates are laid out, each predicate has rules spelling out
which of Unknown, True, or False results when an error condition is
encountered (usually Unknown, except where something already seen allows
returning True or False). Finally, the <JSON filter expression> itself
collapses the three-valued logic to two; it includes the items for which
the predicate returns True, and excludes them for False or Unknown.

So that's where the errors went.

The question of what should happen to the errors when a
<JSON path predicate> appears outside of a <JSON filter expression>
of course isn't answered in the standard, because that's not supposed
to be possible. So if we're allowing predicates to appear on their own
as expressions, it's also up to us to say what should happen with errors
when they do.

Regards,
-Chap