pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
13+ messages / 2 participants
[nested] [flat]
* Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
@ 2022-11-24 05:31 David G. Johnston <david.g.johnston@gmail.com>
2022-12-01 20:43 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: David G. Johnston @ 2022-11-24 05:31 UTC (permalink / raw)
To: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Hey,
There is supposedly a recently submitted (i.e., in moderation) bug report
from a Slack member on this as well, but I decided I didn't want to wait
for it to post.
The following query produces an incorrect result. It should error (or at
worse produce "false"), but it instead produces "true" (this applies to @?
too)
select jsonb_path_exists('{"foo": true}'::jsonb, '$bar', '{}', false);
The corresponding:
select jsonb_path_match('{"foo": true}'::jsonb, '$bar', '{}', false);
produces the expected <ERROR: could not find jsonpath variable "bar">
The responsible code seems to be (just did some code skimming here):
src/backend/utils/adt/jsonpath_exec.c@executeItemOptUnwrapTarget
https://github.com/postgres/postgres/blob/a601366a460f68472bf70c4d94c57baa0a3ed1b2/src/backend/utils...
case jpiVariable:
{
JsonbValue vbuf;
JsonbValue *v;
bool hasNext = jspGetNext(jsp, &elem);
if (!hasNext && !found)
{
res = jperOk; /* skip evaluation */
break;
}
v = hasNext ? &vbuf : palloc(sizeof(*v));
baseObject = cxt->baseObject;
getJsonPathItem(cxt, jsp, v);
res = executeNextItem(cxt, jsp, &elem,
v, found, hasNext);
cxt->baseObject = baseObject;
}
break;
Specifically, since exists doesn't care about values, just presence, found
is false, and since the variable is the only thing present, hasNext is also
false. Thus we simply return jperOK without ever checking to see what the
variable actually is. This results in the exists code producing a true
result.
Looking at this more, it isn't just the variable case that ends up
producing the wrong answer. Going by the principle that any function call
of jsonb_path_exists that returns true should produce said match when
executing jsonb_path_match, this is also broken for the rest (probably) of
the matched types in the case group. And indeed, if the variable "bar" is
defined the error in the match case just changes to "single boolean result
is expected".
select jsonb_path_exists('{"foo": true}'::jsonb, '"bar"', '{}', false); --
true (bar in double quotes)
select jsonb_path_match('{"foo": true}'::jsonb, '"bar"', '{}', false);
-- ERROR: single boolean result is expected
select jsonb_path_match('{"foo": true}'::jsonb, '$bar', '{"bar":"foo"}',
false); -- same error as above, as expected
I expect the missing variable specification to produce jperError and the
rest of the block to produce jperNotFound. The "single boolean result
expected" error seems incorrect though I'm not sure where that is coming
from. But I'm also not considering, or am even aware of, what the standard
we are guided by here says should actually happen.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
@ 2022-12-01 20:43 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: David G. Johnston @ 2022-12-01 20:43 UTC (permalink / raw)
To: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>
Ping (+ cc'ing Alexander who committed this)
On Wed, Nov 23, 2022 at 10:31 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> Hey,
>
> There is supposedly a recently submitted (i.e., in moderation) bug report
> from a Slack member on this as well, but I decided I didn't want to wait
> for it to post.
>
>
> The following query produces an incorrect result. It should error (or at
> worse produce "false"), but it instead produces "true" (this applies to @?
> too)
>
> select jsonb_path_exists('{"foo": true}'::jsonb, '$bar', '{}', false);
>
> The corresponding:
>
> select jsonb_path_match('{"foo": true}'::jsonb, '$bar', '{}', false);
>
> produces the expected <ERROR: could not find jsonpath variable "bar">
>
> The responsible code seems to be (just did some code skimming here):
>
> src/backend/utils/adt/jsonpath_exec.c@executeItemOptUnwrapTarget
>
> https://github.com/postgres/postgres/blob/a601366a460f68472bf70c4d94c57baa0a3ed1b2/src/backend/utils...
>
> case jpiVariable:
> {
> JsonbValue vbuf;
> JsonbValue *v;
> bool hasNext = jspGetNext(jsp, &elem);
>
> if (!hasNext && !found)
> {
> res = jperOk; /* skip evaluation */
> break;
> }
>
> v = hasNext ? &vbuf : palloc(sizeof(*v));
>
> baseObject = cxt->baseObject;
> getJsonPathItem(cxt, jsp, v);
>
> res = executeNextItem(cxt, jsp, &elem,
> v, found, hasNext);
> cxt->baseObject = baseObject;
> }
> break;
>
> Specifically, since exists doesn't care about values, just presence, found
> is false, and since the variable is the only thing present, hasNext is also
> false. Thus we simply return jperOK without ever checking to see what the
> variable actually is. This results in the exists code producing a true
> result.
>
> Looking at this more, it isn't just the variable case that ends up
> producing the wrong answer. Going by the principle that any function call
> of jsonb_path_exists that returns true should produce said match when
> executing jsonb_path_match, this is also broken for the rest (probably) of
> the matched types in the case group. And indeed, if the variable "bar" is
> defined the error in the match case just changes to "single boolean result
> is expected".
>
> select jsonb_path_exists('{"foo": true}'::jsonb, '"bar"', '{}', false); --
> true (bar in double quotes)
> select jsonb_path_match('{"foo": true}'::jsonb, '"bar"', '{}', false);
> -- ERROR: single boolean result is expected
> select jsonb_path_match('{"foo": true}'::jsonb, '$bar', '{"bar":"foo"}',
> false); -- same error as above, as expected
>
> I expect the missing variable specification to produce jperError and the
> rest of the block to produce jperNotFound. The "single boolean result
> expected" error seems incorrect though I'm not sure where that is coming
> from. But I'm also not considering, or am even aware of, what the standard
> we are guided by here says should actually happen.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
@ 2022-12-02 12:18 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 14:24 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-05 23:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: Alexander Korotkov @ 2022-12-02 12:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Hi, David!
Thank you for the report.
On Thu, Nov 24, 2022 at 8:31 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> The following query produces an incorrect result. It should error (or at worse produce "false"), but it instead produces "true" (this applies to @? too)
>
> select jsonb_path_exists('{"foo": true}'::jsonb, '$bar', '{}', false);
Yes, this definitely looks incorrect.
> Specifically, since exists doesn't care about values, just presence, found is false, and since the variable is the only thing present, hasNext is also false. Thus we simply return jperOK without ever checking to see what the variable actually is. This results in the exists code producing a true result.
>
> Looking at this more, it isn't just the variable case that ends up producing the wrong answer. Going by the principle that any function call of jsonb_path_exists that returns true should produce said match when executing jsonb_path_match, this is also broken for the rest (probably) of the matched types in the case group. And indeed, if the variable "bar" is defined the error in the match case just changes to "single boolean result is expected".
Variable case is definitely broken, but I don't think other cases are
broken. If we're checking for existence and there is a constant, we
can immediately return true because constant exists indeed. That
logic doesn't work for variable, which could be non-existent.
> select jsonb_path_exists('{"foo": true}'::jsonb, '"bar"', '{}', false); -- true (bar in double quotes)
> select jsonb_path_match('{"foo": true}'::jsonb, '"bar"', '{}', false); -- ERROR: single boolean result is expected
> select jsonb_path_match('{"foo": true}'::jsonb, '$bar', '{"bar":"foo"}', false); -- same error as above, as expected
>
> I expect the missing variable specification to produce jperError and the rest of the block to produce jperNotFound. The "single boolean result expected" error seems incorrect though I'm not sure where that is coming from. But I'm also not considering, or am even aware of, what the standard we are guided by here says should actually happen.
I think jsonb_path_match() behaves correctly, it expects jsonpatch
expression to return single boolend and throws an error otherwise.
BTW, do you mean something like this: jsonb_path_match() equivalent to
jsonb_path_match() expression?
select jsonb_path_match('{"foo": true}'::jsonb, 'exists($bar)',
'{"bar":"foo"}', false);
Draft patch fixing the issue is attached. Let me know what you think
about this.
------
Regards,
Alexander Korotkov
Attachments:
fix_jsonb_variable.patch (application/octet-stream, 3.2K) download
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 8d83b2edb35..145a5e75f6b 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -224,8 +224,9 @@ static JsonPathExecResult appendBoolResult(JsonPathExecContext *cxt,
JsonPathItem *jsp, JsonValueList *found, JsonPathBool res);
static void getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
JsonbValue *value);
-static void getJsonPathVariable(JsonPathExecContext *cxt,
- JsonPathItem *variable, Jsonb *vars, JsonbValue *value);
+static JsonPathExecResult getJsonPathVariable(JsonPathExecContext *cxt,
+ JsonPathItem *variable,
+ JsonbValue *value);
static int JsonbArraySize(JsonbValue *jb);
static JsonPathBool executeComparison(JsonPathItem *cmp, JsonbValue *lv,
JsonbValue *rv, void *p);
@@ -952,7 +953,6 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
case jpiBool:
case jpiNumeric:
case jpiString:
- case jpiVariable:
{
JsonbValue vbuf;
JsonbValue *v;
@@ -975,6 +975,28 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
}
break;
+ case jpiVariable:
+ {
+ JsonbValue vbuf;
+ JsonbValue *v;
+ bool hasNext = jspGetNext(jsp, &elem);
+
+ v = hasNext ? &vbuf : palloc(sizeof(*v));
+
+ baseObject = cxt->baseObject;
+ res = getJsonPathVariable(cxt, jsp, v);
+
+ if (res == jperOk)
+ {
+ res = executeNextItem(cxt, jsp, &elem,
+ v, found, hasNext);
+ if (jspHasNext(jsp) || !found)
+ pfree(v);
+ }
+ cxt->baseObject = baseObject;
+ }
+ break;
+
case jpiType:
{
JsonbValue *jbv = palloc(sizeof(*jbv));
@@ -2092,9 +2114,6 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
value->val.string.val = jspGetString(item,
&value->val.string.len);
break;
- case jpiVariable:
- getJsonPathVariable(cxt, item, cxt->vars, value);
- return;
default:
elog(ERROR, "unexpected jsonpath item type");
}
@@ -2103,19 +2122,20 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
/*
* Get the value of variable passed to jsonpath executor
*/
-static void
+static JsonPathExecResult
getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable,
- Jsonb *vars, JsonbValue *value)
+ JsonbValue *value)
{
char *varName;
int varNameLength;
JsonbValue tmp;
JsonbValue *v;
+ Jsonb *vars = cxt->vars;
+ JsonPathExecResult res;
if (!vars)
{
- value->type = jbvNull;
- return;
+ return jperNotFound;
}
Assert(variable->type == jpiVariable);
@@ -2130,17 +2150,26 @@ getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable,
{
*value = *v;
pfree(v);
+ res = jperOk;
}
- else
+ else if (!jspIgnoreStructuralErrors(cxt))
{
+ if (!jspThrowErrors(cxt))
+ res = jperError;
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("could not find jsonpath variable \"%s\"",
pnstrdup(varName, varNameLength))));
}
+ else
+ {
+ res = jperNotFound;
+ }
JsonbInitBinary(&tmp, vars);
setBaseObject(cxt, &tmp, 1);
+
+ return res;
}
/**************** Support functions for JsonPath execution *****************/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
@ 2022-12-02 14:24 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 14:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2022-12-02 14:24 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Fri, Dec 2, 2022 at 5:18 AM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
> On Thu, Nov 24, 2022 at 8:31 AM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> > The following query produces an incorrect result. It should error (or
> at worse produce "false"), but it instead produces "true" (this applies to
> @? too)
> >
> > select jsonb_path_exists('{"foo": true}'::jsonb, '$bar', '{}', false);
>
> Variable case is definitely broken, but I don't think other cases are
> broken. If we're checking for existence and there is a constant, we
> can immediately return true because constant exists indeed. That
> logic doesn't work for variable, which could be non-existent.
>
> > select jsonb_path_exists('{"foo": true}'::jsonb, '"bar"', '{}', false);
> -- true (bar in double quotes)
>
I think my issue with the constant is that the function itself is said to
return whether or not the provided path matches the input json. It is
impossible to match the input json if there is no reference to the input
json in the jsonpath expression. As the existing wording promises: "Checks
whether the JSON path returns any item for the specified JSON value" - the
word item is rightly taken to mean that the path at minimum references the
root (i.e., mandatory $) - and that any true result from exists will, if
the expression is used for _match, produce the "item for the specified JSON
value" that was found.
So I'll stand by my conclusion that the behavior of constants is buggy -
though I suppose fixing the bug is probably most readily accomplished by
changing the definition of what behavior we are promising and fixing up the
documentation to express that change. In short, it is really an error to
not specify "$" in your expression - but if you don't you will simply get a
true outcome for the existence test - for backward compatibility reasons.
> > select jsonb_path_match('{"foo": true}'::jsonb, '"bar"', '{}', false);
> -- ERROR: single boolean result is expected
> > select jsonb_path_match('{"foo": true}'::jsonb, '$bar', '{"bar":"foo"}',
> false); -- same error as above, as expected
> >
> > I expect the missing variable specification to produce jperError and the
> rest of the block to produce jperNotFound. The "single boolean result
> expected" error seems incorrect though I'm not sure where that is coming
> from. But I'm also not considering, or am even aware of, what the standard
> we are guided by here says should actually happen.
>
> I think jsonb_path_match() behaves correctly, it expects jsonpatch
> expression to return single boolend and throws an error otherwise.
>
Yeah, I may have mis-interpreted the meaning of the error message.
Something like: "jsonpath expression must produce a single boolean result"
would be a bit more clear.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 14:24 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
@ 2022-12-02 14:57 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 17:47 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alexander Korotkov @ 2022-12-02 14:57 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Fri, Dec 2, 2022 at 5:24 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Fri, Dec 2, 2022 at 5:18 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>>
>> On Thu, Nov 24, 2022 at 8:31 AM David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>> > The following query produces an incorrect result. It should error (or at worse produce "false"), but it instead produces "true" (this applies to @? too)
>> >
>> > select jsonb_path_exists('{"foo": true}'::jsonb, '$bar', '{}', false);
>>
>> Variable case is definitely broken, but I don't think other cases are
>> broken. If we're checking for existence and there is a constant, we
>> can immediately return true because constant exists indeed. That
>> logic doesn't work for variable, which could be non-existent.
>>
>> > select jsonb_path_exists('{"foo": true}'::jsonb, '"bar"', '{}', false); -- true (bar in double quotes)
>
>
> I think my issue with the constant is that the function itself is said to return whether or not the provided path matches the input json. It is impossible to match the input json if there is no reference to the input json in the jsonpath expression. As the existing wording promises: "Checks whether the JSON path returns any item for the specified JSON value" - the word item is rightly taken to mean that the path at minimum references the root (i.e., mandatory $) - and that any true result from exists will, if the expression is used for _match, produce the "item for the specified JSON value" that was found.
>
> So I'll stand by my conclusion that the behavior of constants is buggy - though I suppose fixing the bug is probably most readily accomplished by changing the definition of what behavior we are promising and fixing up the documentation to express that change. In short, it is really an error to not specify "$" in your expression - but if you don't you will simply get a true outcome for the existence test - for backward compatibility reasons.
Thank you for explaining your point, but I can't agree with that.
Constant jsonpath expression is always returning item for the input
JSON value. Even despite the input value is ignored. This is
redundant case, but still correct.
>> > select jsonb_path_match('{"foo": true}'::jsonb, '"bar"', '{}', false); -- ERROR: single boolean result is expected
>> > select jsonb_path_match('{"foo": true}'::jsonb, '$bar', '{"bar":"foo"}', false); -- same error as above, as expected
>> >
>> > I expect the missing variable specification to produce jperError and the rest of the block to produce jperNotFound. The "single boolean result expected" error seems incorrect though I'm not sure where that is coming from. But I'm also not considering, or am even aware of, what the standard we are guided by here says should actually happen.
>>
>> I think jsonb_path_match() behaves correctly, it expects jsonpatch
>> expression to return single boolend and throws an error otherwise.
>
> Yeah, I may have mis-interpreted the meaning of the error message. Something like: "jsonpath expression must produce a single boolean result" would be a bit more clear.
OK, I'm not a native English speaker and can't judge about this. I
propose this should be considered separately.
BTW, what do you think about the patch?
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 14:24 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 14:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
@ 2022-12-02 17:47 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 19:40 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alexander Korotkov @ 2022-12-02 17:47 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Fri, Dec 2, 2022 at 5:57 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On Fri, Dec 2, 2022 at 5:24 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> > On Fri, Dec 2, 2022 at 5:18 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >>
> >> On Thu, Nov 24, 2022 at 8:31 AM David G. Johnston
> >> <david.g.johnston@gmail.com> wrote:
> >> > The following query produces an incorrect result. It should error (or at worse produce "false"), but it instead produces "true" (this applies to @? too)
> >> >
> >> > select jsonb_path_exists('{"foo": true}'::jsonb, '$bar', '{}', false);
> >>
> >> Variable case is definitely broken, but I don't think other cases are
> >> broken. If we're checking for existence and there is a constant, we
> >> can immediately return true because constant exists indeed. That
> >> logic doesn't work for variable, which could be non-existent.
> >>
> >> > select jsonb_path_exists('{"foo": true}'::jsonb, '"bar"', '{}', false); -- true (bar in double quotes)
> >
> >
> > I think my issue with the constant is that the function itself is said to return whether or not the provided path matches the input json. It is impossible to match the input json if there is no reference to the input json in the jsonpath expression. As the existing wording promises: "Checks whether the JSON path returns any item for the specified JSON value" - the word item is rightly taken to mean that the path at minimum references the root (i.e., mandatory $) - and that any true result from exists will, if the expression is used for _match, produce the "item for the specified JSON value" that was found.
> >
> > So I'll stand by my conclusion that the behavior of constants is buggy - though I suppose fixing the bug is probably most readily accomplished by changing the definition of what behavior we are promising and fixing up the documentation to express that change. In short, it is really an error to not specify "$" in your expression - but if you don't you will simply get a true outcome for the existence test - for backward compatibility reasons.
>
> Thank you for explaining your point, but I can't agree with that.
> Constant jsonpath expression is always returning item for the input
> JSON value. Even despite the input value is ignored. This is
> redundant case, but still correct.
Let me explain more what I do mean. In the SQL SELECT statement there
is a WHERE clause. This clause should express the predicate, which
should match to rows. But you're writing "WHERE 1 = 1" or "WHERE
true" then all rows are matching even that no column is referenced.
This is how SQL is working. And I see no reason why jsonpath should
work in a different way.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 14:24 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 14:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 17:47 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
@ 2022-12-02 19:40 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 22:31 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2022-12-02 19:40 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Fri, Dec 2, 2022 at 10:47 AM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
>
> > Thank you for explaining your point, but I can't agree with that.
> > Constant jsonpath expression is always returning item for the input
> > JSON value. Even despite the input value is ignored. This is
> > redundant case, but still correct.
>
> Let me explain more what I do mean. In the SQL SELECT statement there
> is a WHERE clause. This clause should express the predicate, which
> should match to rows. But you're writing "WHERE 1 = 1" or "WHERE
> true" then all rows are matching even that no column is referenced.
> This is how SQL is working. And I see no reason why jsonpath should
> work in a different way.
>
>
I like the analogy but it seems to support my conclusion moreso than yours:
Consider: select jsonb_path_exists('{"foo":"bar"}'::jsonb, 'false');
The analogous SQL query is: "SELECT * FROM table WHERE false" would
indeed produce an empty set - which EXISTS would interpret as false but you
want to evaluate to true
Or, "SELECT * FROM table WHERE 'banana';" which produces the same kind of
error that I wish jsonb_path_exists would produce when one writes a
similarly nonsensical path.
David J.
I'll probably get to a formal review of the patch - but actually I am
hoping someone else more comfortable in the codebase chimes in here with an
opinion. Though as I said, I'm willing to concede that the behavior should
probably stay unchanged, for compatibility reasons, and we just need to
decide on how to correctly document this.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 14:24 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 14:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 17:47 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 19:40 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
@ 2022-12-02 22:31 ` Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Alexander Korotkov @ 2022-12-02 22:31 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Fri, Dec 2, 2022 at 10:40 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Fri, Dec 2, 2022 at 10:47 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> > Thank you for explaining your point, but I can't agree with that.
>> > Constant jsonpath expression is always returning item for the input
>> > JSON value. Even despite the input value is ignored. This is
>> > redundant case, but still correct.
>>
>> Let me explain more what I do mean. In the SQL SELECT statement there
>> is a WHERE clause. This clause should express the predicate, which
>> should match to rows. But you're writing "WHERE 1 = 1" or "WHERE
>> true" then all rows are matching even that no column is referenced.
>> This is how SQL is working. And I see no reason why jsonpath should
>> work in a different way.
>
> I like the analogy but it seems to support my conclusion moreso than yours:
>
> Consider: select jsonb_path_exists('{"foo":"bar"}'::jsonb, 'false');
>
> The analogous SQL query is: "SELECT * FROM table WHERE false" would indeed produce an empty set - which EXISTS would interpret as false but you want to evaluate to true
>
> Or, "SELECT * FROM table WHERE 'banana';" which produces the same kind of error that I wish jsonb_path_exists would produce when one writes a similarly nonsensical path.
I think this is cross-analogy existing to matching, which doesn't
work. jsonb_path_exists() has existence symantic, while simple where
clause doesn't.
I think
"select jsonb_path_match('{"foo":"bar"}'::jsonb, 'false');"
is equivalent to
"SELECT * FROM table WHERE false;"
"select jsonb_path_exists('{"foo":"bar"}'::jsonb, '"match"');"
is equivalent to
"SELECT * FROM table WHERE EXISTS (SELECT 'match');"
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
@ 2022-12-05 23:57 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-07 22:52 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Alexander Korotkov @ 2022-12-05 23:57 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Fri, Dec 2, 2022 at 3:18 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> Draft patch fixing the issue is attached. Let me know what you think
> about this.
Revised patch is attached, wrong pfree() is fixed. I was intended to
backpatch it. But the behavior change makes me uneasy.
select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)');
Currently, this query generates an error because of missing "value"
variable. The patch suppress this error. I'm not sure this error
should be suppressed. Especially, I'm sure this should be
backpatched.
Should we fix only existence checking behaviour and let other cases
throw an error? Thoughts?
------
Regards,
Alexander Korotkov
Attachments:
0001-Teach-jsonpath-to-handle-missing-variables-v2.patch (application/octet-stream, 4.9K) download
From 042ae7d0cfdb8683e1a07f691a21e47d17ea2b39 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Mon, 5 Dec 2022 17:02:29 +0300
Subject: [PATCH] Teach jsonpath to handle missing variables
The current jsonpath code assumes that the referenced variable always exists.
It could only throw an error at the value valuation time. At the same time
existence checking assumes variable is present without valuation, and error
suppression doesn't work for missing variables.
This commit makes jsonpath code to handle variables in the similar way to
key accessors. The existence is now checked runtime, and error suppression is
also applied.
Backpatch to 12 where jsonpath was introduced.
Reported-by: David G. Johnston
Discussion: https://postgr.es/m/CAKFQuwbeytffJkVnEqDyLZ%3DrQsznoTh1OgDoOF3VmOMkxcTMjA%40mail.gmail.com
Author: Alexander Korotkov
Backpatch-through: 12
---
src/backend/utils/adt/jsonpath_exec.c | 47 +++++++++++++++-----
src/test/regress/expected/jsonb_jsonpath.out | 5 ++-
2 files changed, 40 insertions(+), 12 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 8d83b2edb35..e15ced19c4e 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -224,8 +224,9 @@ static JsonPathExecResult appendBoolResult(JsonPathExecContext *cxt,
JsonPathItem *jsp, JsonValueList *found, JsonPathBool res);
static void getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
JsonbValue *value);
-static void getJsonPathVariable(JsonPathExecContext *cxt,
- JsonPathItem *variable, Jsonb *vars, JsonbValue *value);
+static JsonPathExecResult getJsonPathVariable(JsonPathExecContext *cxt,
+ JsonPathItem *variable,
+ JsonbValue *value);
static int JsonbArraySize(JsonbValue *jb);
static JsonPathBool executeComparison(JsonPathItem *cmp, JsonbValue *lv,
JsonbValue *rv, void *p);
@@ -952,7 +953,6 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
case jpiBool:
case jpiNumeric:
case jpiString:
- case jpiVariable:
{
JsonbValue vbuf;
JsonbValue *v;
@@ -975,6 +975,24 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
}
break;
+ case jpiVariable:
+ {
+ JsonbValue vbuf;
+ JsonbValue *v;
+ bool hasNext = jspGetNext(jsp, &elem);
+
+ v = hasNext ? &vbuf : palloc(sizeof(*v));
+
+ baseObject = cxt->baseObject;
+ res = getJsonPathVariable(cxt, jsp, v);
+
+ if (res == jperOk)
+ res = executeNextItem(cxt, jsp, &elem,
+ v, found, hasNext);
+ cxt->baseObject = baseObject;
+ }
+ break;
+
case jpiType:
{
JsonbValue *jbv = palloc(sizeof(*jbv));
@@ -2092,9 +2110,6 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
value->val.string.val = jspGetString(item,
&value->val.string.len);
break;
- case jpiVariable:
- getJsonPathVariable(cxt, item, cxt->vars, value);
- return;
default:
elog(ERROR, "unexpected jsonpath item type");
}
@@ -2103,19 +2118,20 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
/*
* Get the value of variable passed to jsonpath executor
*/
-static void
+static JsonPathExecResult
getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable,
- Jsonb *vars, JsonbValue *value)
+ JsonbValue *value)
{
char *varName;
int varNameLength;
JsonbValue tmp;
JsonbValue *v;
+ Jsonb *vars = cxt->vars;
+ JsonPathExecResult res;
if (!vars)
{
- value->type = jbvNull;
- return;
+ return jperNotFound;
}
Assert(variable->type == jpiVariable);
@@ -2130,17 +2146,26 @@ getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable,
{
*value = *v;
pfree(v);
+ res = jperOk;
}
- else
+ else if (!jspIgnoreStructuralErrors(cxt))
{
+ if (!jspThrowErrors(cxt))
+ res = jperError;
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("could not find jsonpath variable \"%s\"",
pnstrdup(varName, varNameLength))));
}
+ else
+ {
+ res = jperNotFound;
+ }
JsonbInitBinary(&tmp, vars);
setBaseObject(cxt, &tmp, 1);
+
+ return res;
}
/**************** Support functions for JsonPath execution *****************/
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 508ddd797ed..19c6bdc72c7 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -486,7 +486,10 @@ select * from jsonb_path_query('{"a": 10}', '$');
(1 row)
select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)');
-ERROR: could not find jsonpath variable "value"
+ jsonb_path_query
+------------------
+(0 rows)
+
select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)', '1');
ERROR: "vars" argument is not an object
DETAIL: Jsonpath parameters should be encoded as key-value pairs of "vars" object.
--
2.24.3 (Apple Git-128)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-05 23:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
@ 2022-12-07 22:52 ` David G. Johnston <david.g.johnston@gmail.com>
2023-01-08 11:19 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2022-12-07 22:52 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Mon, Dec 5, 2022 at 4:58 PM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
> On Fri, Dec 2, 2022 at 3:18 PM Alexander Korotkov <aekorotkov@gmail.com>
> wrote:
> > Draft patch fixing the issue is attached. Let me know what you think
> > about this.
>
> Revised patch is attached, wrong pfree() is fixed. I was intended to
> backpatch it. But the behavior change makes me uneasy.
>
> select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)');
>
> Currently, this query generates an error because of missing "value"
> variable. The patch suppress this error. I'm not sure this error
> should be suppressed. Especially, I'm sure this should be
> backpatched.
>
> Should we fix only existence checking behaviour and let other cases
> throw an error? Thoughts?
>
>
I've attached some additional regression test changes to formally document
what it is we are affecting here. The "false" ones seems like it can
stand-in for all of the types left behind when the variable one got moved
to its own case.
The regressions.diffs file is the changes made by the 0001 patch.
Instead of making everything that today correctly produces a "could not
find jsonpath variable" error behave in a non-error way we need to make
_exists produce the exact same error. Aside from seemingly being correct
on its own merits, it is superior to turning what was a true outcome to a
false outcome, which is much more likely to go unnoticed and cause people
grief.
I feel like we are not adequately testing the "jspGetNext" true outcome of
the variable path but I still haven't fully gotten my head around the code.
The behavior of the introduced constant false jsonpath expression seems
internally consistent. Fixing the documentation to make it clear how such
an unusual but acceptable jsonpath expression behaves is material for a
separate patch.
David J.
Attachments:
regression.diffs (application/octet-stream, 1.8K) download
0000-v1-Add-regression-tests-demonstrating-scalar-jsonpath-b.patch (application/octet-stream, 4.8K) download
From 48bfa0fd1c4c45d727b29e1dfd0f2a9ce3d39afa Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Wed, 7 Dec 2022 22:28:46 +0000
Subject: [PATCH] Add regression tests demonstrating scalar jsonpath behavior
---
src/test/regress/expected/jsonb_jsonpath.out | 36 ++++++++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 8 +++++
2 files changed, 44 insertions(+)
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 508ddd797e..5fbbc8519d 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2212,6 +2212,14 @@ SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)');
------------------
(0 rows)
+SELECT jsonb_path_query('[{"a": 1}]', '$undefined_var');
+ERROR: could not find jsonpath variable "undefined_var"
+SELECT jsonb_path_query('[{"a": 1}]', 'false');
+ jsonb_path_query
+------------------
+ false
+(1 row)
+
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
ERROR: JSON object does not contain key "a"
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a');
@@ -2282,6 +2290,14 @@ SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].
(1 row)
+SELECT jsonb_path_query_first('[{"a": 1}]', '$undefined_var');
+ERROR: could not find jsonpath variable "undefined_var"
+SELECT jsonb_path_query_first('[{"a": 1}]', 'false');
+ jsonb_path_query_first
+------------------------
+ false
+(1 row)
+
SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)';
?column?
----------
@@ -2312,6 +2328,18 @@ SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.
f
(1 row)
+SELECT jsonb_path_exists('[{"a": 1}]', '$undefined_var');
+ jsonb_path_exists
+-------------------
+ t
+(1 row)
+
+SELECT jsonb_path_exists('[{"a": 1}]', 'false');
+ jsonb_path_exists
+-------------------
+ t
+(1 row)
+
SELECT jsonb_path_match('true', '$', silent => false);
jsonb_path_match
------------------
@@ -2374,6 +2402,14 @@ SELECT jsonb_path_match('[{"a": 1}, {"a": 2}]', '$[*].a > 1');
t
(1 row)
+SELECT jsonb_path_match('[{"a": 1}]', '$undefined_var');
+ERROR: could not find jsonpath variable "undefined_var"
+SELECT jsonb_path_match('[{"a": 1}]', 'false');
+ jsonb_path_match
+------------------
+ f
+(1 row)
+
-- test string comparison (Unicode codepoint collation)
WITH str(j, num) AS
(
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 60f73cb059..bd025077d5 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -532,6 +532,8 @@ set time zone default;
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]');
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)');
+SELECT jsonb_path_query('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_query('[{"a": 1}]', 'false');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a');
@@ -547,12 +549,16 @@ SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}');
+SELECT jsonb_path_query_first('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_query_first('[{"a": 1}]', 'false');
SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)';
SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)';
SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 1)');
SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 1, "max": 4}');
SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 3, "max": 4}');
+SELECT jsonb_path_exists('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_exists('[{"a": 1}]', 'false');
SELECT jsonb_path_match('true', '$', silent => false);
SELECT jsonb_path_match('false', '$', silent => false);
@@ -569,6 +575,8 @@ SELECT jsonb_path_match('[true, true]', '$[*]', silent => false);
SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 1';
SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 2';
SELECT jsonb_path_match('[{"a": 1}, {"a": 2}]', '$[*].a > 1');
+SELECT jsonb_path_match('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_match('[{"a": 1}]', 'false');
-- test string comparison (Unicode codepoint collation)
WITH str(j, num) AS
--
2.25.1
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-05 23:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-07 22:52 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
@ 2023-01-08 11:19 ` Alexander Korotkov <aekorotkov@gmail.com>
2023-01-12 15:31 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alexander Korotkov @ 2023-01-08 11:19 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Thu, Dec 8, 2022 at 1:52 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Mon, Dec 5, 2022 at 4:58 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>>
>> On Fri, Dec 2, 2022 at 3:18 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> > Draft patch fixing the issue is attached. Let me know what you think
>> > about this.
>>
>> Revised patch is attached, wrong pfree() is fixed. I was intended to
>> backpatch it. But the behavior change makes me uneasy.
>>
>> select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)');
>>
>> Currently, this query generates an error because of missing "value"
>> variable. The patch suppress this error. I'm not sure this error
>> should be suppressed. Especially, I'm sure this should be
>> backpatched.
>>
>> Should we fix only existence checking behaviour and let other cases
>> throw an error? Thoughts?
>>
>
> I've attached some additional regression test changes to formally document what it is we are affecting here. The "false" ones seems like it can stand-in for all of the types left behind when the variable one got moved to its own case.
>
> The regressions.diffs file is the changes made by the 0001 patch.
>
> Instead of making everything that today correctly produces a "could not find jsonpath variable" error behave in a non-error way we need to make _exists produce the exact same error. Aside from seemingly being correct on its own merits, it is superior to turning what was a true outcome to a false outcome, which is much more likely to go unnoticed and cause people grief.
This makes sense to me. See the attached patch implementing this.
I'm going to push and backpatch it if no objections.
> The behavior of the introduced constant false jsonpath expression seems internally consistent. Fixing the documentation to make it clear how such an unusual but acceptable jsonpath expression behaves is material for a separate patch.
I would appreciate if you could work on such patch. If so, feel free
to post it.
------
Regards,
Alexander Korotkov
Attachments:
0001-Fix-jsonpath-existense-checking-of-missing-variab-v3.patch (application/octet-stream, 6.1K) download
From 2928df5570eb70932aa85f056f185d01d2d342a6 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Wed, 7 Dec 2022 22:28:46 +0000
Subject: [PATCH] Fix jsonpath existense checking of missing variables
The current jsonpath code assumes that the referenced variable always exists.
It could only throw an error at the value valuation time. At the same time
existence checking assumes variable is present without valuation, and error
suppression doesn't work for missing variables.
This commit makes existense checking trigger an error for missing variables.
This makes the overall behavior consistent.
Backpatch to 12 where jsonpath was introduced.
Reported-by: David G. Johnston
Discussion: https://postgr.es/m/CAKFQuwbeytffJkVnEqDyLZ%3DrQsznoTh1OgDoOF3VmOMkxcTMjA%40mail.gmail.com
Author: Alexander Korotkov, David G. Johnston
Backpatch-through: 12
---
src/backend/utils/adt/jsonpath_exec.c | 8 +++--
src/test/regress/expected/jsonb_jsonpath.out | 32 ++++++++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 8 +++++
3 files changed, 46 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 350e543fd9c..c2b276c1b7b 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -959,9 +959,13 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
JsonbValue *v;
bool hasNext = jspGetNext(jsp, &elem);
- if (!hasNext && !found)
+ if (!hasNext && !found && jsp->type != jpiVariable)
{
- res = jperOk; /* skip evaluation */
+ /*
+ * Skip evaluation, but not for variables. We must trigger
+ * an error for the missing variable.
+ */
+ res = jperOk;
break;
}
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 508ddd797ed..328a6b39199 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2212,6 +2212,14 @@ SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)');
------------------
(0 rows)
+SELECT jsonb_path_query('[{"a": 1}]', '$undefined_var');
+ERROR: could not find jsonpath variable "undefined_var"
+SELECT jsonb_path_query('[{"a": 1}]', 'false');
+ jsonb_path_query
+------------------
+ false
+(1 row)
+
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
ERROR: JSON object does not contain key "a"
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a');
@@ -2282,6 +2290,14 @@ SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].
(1 row)
+SELECT jsonb_path_query_first('[{"a": 1}]', '$undefined_var');
+ERROR: could not find jsonpath variable "undefined_var"
+SELECT jsonb_path_query_first('[{"a": 1}]', 'false');
+ jsonb_path_query_first
+------------------------
+ false
+(1 row)
+
SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)';
?column?
----------
@@ -2312,6 +2328,14 @@ SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.
f
(1 row)
+SELECT jsonb_path_exists('[{"a": 1}]', '$undefined_var');
+ERROR: could not find jsonpath variable "undefined_var"
+SELECT jsonb_path_exists('[{"a": 1}]', 'false');
+ jsonb_path_exists
+-------------------
+ t
+(1 row)
+
SELECT jsonb_path_match('true', '$', silent => false);
jsonb_path_match
------------------
@@ -2374,6 +2398,14 @@ SELECT jsonb_path_match('[{"a": 1}, {"a": 2}]', '$[*].a > 1');
t
(1 row)
+SELECT jsonb_path_match('[{"a": 1}]', '$undefined_var');
+ERROR: could not find jsonpath variable "undefined_var"
+SELECT jsonb_path_match('[{"a": 1}]', 'false');
+ jsonb_path_match
+------------------
+ f
+(1 row)
+
-- test string comparison (Unicode codepoint collation)
WITH str(j, num) AS
(
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 60f73cb0590..bd025077d52 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -532,6 +532,8 @@ set time zone default;
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]');
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)');
+SELECT jsonb_path_query('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_query('[{"a": 1}]', 'false');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a');
@@ -547,12 +549,16 @@ SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}');
+SELECT jsonb_path_query_first('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_query_first('[{"a": 1}]', 'false');
SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)';
SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)';
SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 1)');
SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 1, "max": 4}');
SELECT jsonb_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', vars => '{"min": 3, "max": 4}');
+SELECT jsonb_path_exists('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_exists('[{"a": 1}]', 'false');
SELECT jsonb_path_match('true', '$', silent => false);
SELECT jsonb_path_match('false', '$', silent => false);
@@ -569,6 +575,8 @@ SELECT jsonb_path_match('[true, true]', '$[*]', silent => false);
SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 1';
SELECT jsonb '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 2';
SELECT jsonb_path_match('[{"a": 1}, {"a": 2}]', '$[*].a > 1');
+SELECT jsonb_path_match('[{"a": 1}]', '$undefined_var');
+SELECT jsonb_path_match('[{"a": 1}]', 'false');
-- test string comparison (Unicode codepoint collation)
WITH str(j, num) AS
--
2.24.3 (Apple Git-128)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-05 23:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-07 22:52 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2023-01-08 11:19 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
@ 2023-01-12 15:31 ` Alexander Korotkov <aekorotkov@gmail.com>
2023-01-12 15:33 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alexander Korotkov @ 2023-01-12 15:31 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Sun, Jan 8, 2023 at 2:19 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On Thu, Dec 8, 2022 at 1:52 AM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> > On Mon, Dec 5, 2022 at 4:58 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >>
> >> On Fri, Dec 2, 2022 at 3:18 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >> > Draft patch fixing the issue is attached. Let me know what you think
> >> > about this.
> >>
> >> Revised patch is attached, wrong pfree() is fixed. I was intended to
> >> backpatch it. But the behavior change makes me uneasy.
> >>
> >> select * from jsonb_path_query('{"a": 10}', '$ ? (@.a < $value)');
> >>
> >> Currently, this query generates an error because of missing "value"
> >> variable. The patch suppress this error. I'm not sure this error
> >> should be suppressed. Especially, I'm sure this should be
> >> backpatched.
> >>
> >> Should we fix only existence checking behaviour and let other cases
> >> throw an error? Thoughts?
> >>
> >
> > I've attached some additional regression test changes to formally document what it is we are affecting here. The "false" ones seems like it can stand-in for all of the types left behind when the variable one got moved to its own case.
> >
> > The regressions.diffs file is the changes made by the 0001 patch.
> >
> > Instead of making everything that today correctly produces a "could not find jsonpath variable" error behave in a non-error way we need to make _exists produce the exact same error. Aside from seemingly being correct on its own merits, it is superior to turning what was a true outcome to a false outcome, which is much more likely to go unnoticed and cause people grief.
>
> This makes sense to me. See the attached patch implementing this.
> I'm going to push and backpatch it if no objections.
Pushed and backpatched to 12, where jsonpath first appeared.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-05 23:57 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2022-12-07 22:52 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2023-01-08 11:19 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
2023-01-12 15:31 ` Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling Alexander Korotkov <aekorotkov@gmail.com>
@ 2023-01-12 15:33 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: David G. Johnston @ 2023-01-12 15:33 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Thu, Jan 12, 2023 at 8:31 AM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
> Pushed and backpatched to 12, where jsonpath first appeared.
>
>
Thanks. I've created a todo to take a peek at the docs around this.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2023-01-12 15:33 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-24 05:31 Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling David G. Johnston <david.g.johnston@gmail.com>
2022-12-01 20:43 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 12:18 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 14:24 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 14:57 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 17:47 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-02 19:40 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-02 22:31 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-05 23:57 ` Alexander Korotkov <aekorotkov@gmail.com>
2022-12-07 22:52 ` David G. Johnston <david.g.johnston@gmail.com>
2023-01-08 11:19 ` Alexander Korotkov <aekorotkov@gmail.com>
2023-01-12 15:31 ` Alexander Korotkov <aekorotkov@gmail.com>
2023-01-12 15:33 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox