Обсуждение: Variable substitution in jsonb functions fails for jsonpath operator like_regex

Erwin Brandstetter <brsaweda@gmail.com> writes:
> The functions jsonb_path_exists() and friends accept a "vars" parameter for
> parameter substitution in the jsonpath argument. This seems to work for all
> jsonpath operators except "like_regex":

> SELECT * FROM tbl
> WHERE  jsonb_path_exists(data, '$[*].value ? (@ like_regex $foo)', '{"foo":
> "CEO"}');

>> ERROR:  syntax error at or near "$foo" of jsonpath input
>> LINE 3: WHERE  jsonb_path_exists(data, '$[*].value ? (@ like_regex $...

> Notably, the same works even for "starts with":


Hmm, maybe just an oversight in jsonpath_gram.y?

predicate:
    ...
    | expr STARTS_P WITH_P starts_with_initial
    | expr LIKE_REGEX_P STRING_P
    | expr LIKE_REGEX_P STRING_P FLAG_P STRING_P
    ;

starts_with_initial:
    STRING_P                        { $$ = makeItemString(&$1); }
    | VARIABLE_P                    { $$ = makeItemVariable(&$1); }
    ;

Maybe it wouldn't take more than s/STRING_P/starts_with_initial/
here (though I'd then rename starts_with_initial to something
more generic).  With bad luck there might be some dependency on
this restriction downstream of the grammar, but I suspect not.
Didn't try though.

            regards, tom lane

Jeff Janes <jeff.janes@gmail.com> writes:
> On Wed, Oct 18, 2023 at 5:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Maybe it wouldn't take more than s/STRING_P/starts_with_initial/
>> here (though I'd then rename starts_with_initial to something
>> more generic).

> I tried it, and it didn't work.  No error, it just doesn't match
> anything--including literal values which do match things in HEAD.

Oh, duh, makeItemLikeRegex() is expecting a plain JsonPathString,
and JsonPathParseItem.value.like_regex is set up for just a
constant pattern.  We could no doubt fix that, but it's a bigger
lift than I was hoping.

> Maybe the problem is that the regex pattern is compiled at the same time
> the jsonpath is compiled?

Doesn't look that way to me: executeLikeRegex is just using
RE_compile_and_execute every time.  (It's "caching" a text datum
representing the pattern string, which might be a good candidate for
the silliest use of caching I've ever seen in PG; it's surely not
buying any useful increment of performance.)

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Oct 18, 2023, 18:47 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Oh, duh, makeItemLikeRegex() is expecting a plain JsonPathString,
>> and JsonPathParseItem.value.like_regex is set up for just a
>> constant pattern.  We could no doubt fix that, but it's a bigger
>> lift than I was hoping.

> Not in a position to do it myself but we should confirm we aren't simply
> following the standard here, and if so decide whether we want to deviate.

Hmm ... looks like we *are* following the standard:


<JSON like_regex predicate> ::=
  <JSON path wff> like_regex <JSON like_regex pattern>
      [ flag <JSON like_regex flags> ]
<JSON like_regex pattern> ::=
  <JSON path string literal>
<JSON like_regex flag> ::=
  <JSON path string literal>

whereas "starts with" has

<JSON starts with predicate> ::=
  <JSON starts with whole> starts with <JSON starts with initial>
<JSON starts with whole> ::=
  <JSON path wff>
<JSON starts with initial> ::=
  <JSON path wff>

The text mentions that "the second operand is permitted to be an SQL/JSON
sequence and to support existential semantics", whereas they evidently
don't want that for a regex pattern.


            regards, tom lane

Erwin Brandstetter <brsaweda@gmail.com> writes:
> On Thu, 19 Oct 2023 at 21:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm ... looks like we *are* following the standard:
>>   ...
>> The text mentions that "the second operand is permitted to be an SQL/JSON
>> sequence and to support existential semantics", whereas they evidently
>> don't want that for a regex pattern.

> So input from "vars" cannot be substituted into the jsonpath expression
> after "like_regex" (as opposed to all other jsonpath operators). Seems
> pretty random from a user's perspective.

I agree it looks pretty random if you haven't drilled down into the
spec's fine print.  Personally I wouldn't be opposed to extending
the spec here (not that I'm volunteering to write the patch).

Nosing around in jsonpath_gram.y, I see datetime_template as the
only other place where there's a random-seeming choice to allow
STRING_P but not VARIABLE_P.  Should we tackle that too?

            regards, tom lane