[PATCH v1] Match jsonpath variable names exactly when checking mutability

Started by Tomás Senart12 days ago3 messageshackers
Jump to latest
#1Tomás Senart
ts@perfloop.ai

Hi,

Commit 0f271e8e8d9 made GetJsonPathVar() require an exact match between
a jsonpath variable and a PASSING argument name. The mutability check
in jspIsMutableWalker() still compares only the length of the
variable's name, so an earlier PASSING argument named "aa" can decide
the datatype of $a. CREATE INDEX can then accept an expression whose
result depends on TimeZone, or reject an expression that is immutable.

Reproduction on master (952bab8b74d):

CREATE TABLE t (js jsonb, n int, ts timestamp, tz timestamptz);
INSERT INTO t VALUES ('{}', 1, '2024-01-01', '2024-01-01 00:00:00+00');

SET TimeZone = 'UTC';
CREATE INDEX t_idx ON t ((JSON_EXISTS(js, 'lax $ ? ($a == $b)'
PASSING n AS aa, ts AS a, tz AS b)));
-- succeeds; $a is a timestamp and $b a timestamptz, so the
-- comparison depends on TimeZone

SET TimeZone = 'Asia/Tokyo';
SET enable_seqscan = off;
SELECT count(*) FROM t WHERE JSON_EXISTS(js, 'lax $ ? ($a == $b)'
PASSING n AS aa, ts AS a, tz AS b);
-- 1 (Index Scan using t_idx)

RESET enable_seqscan;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*) FROM t WHERE JSON_EXISTS(js, 'lax $ ? ($a == $b)'
PASSING n AS aa, ts AS a, tz AS b);
-- 0 (Seq Scan)

With the patch, the CREATE INDEX fails with "functions in index
expression must be marked IMMUTABLE".

The attached patch also requires equal name lengths in
jspIsMutableWalker(), as GetJsonPathVar() does, and adds two
regression tests to sqljson_queryfuncs. The change adds one strlen()
for each PASSING argument that the check examines; it does not change
how expressions are evaluated. make check and make installcheck pass.
The code came with SQL/JSON in 17, so I think it should be back-patched
there.

Indexes whose definitions were incorrectly accepted as immutable can
return wrong results and should be dropped or replaced with expressions
that are truly immutable. REINDEX does not make those definitions
safe. Restoring their current definitions from a dump will now fail at
CREATE INDEX.

The bug was found and fixed by Perfloop. I am the contact. Feel free
to rework the patch however you prefer.

Regards,
Tomás Senart

Attachments:

t258395_1
v1-0001-Match-jsonpath-variable-names-exactly-when-checki.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Match-jsonpath-variable-names-exactly-when-checki.patchDownload+14-2
#2Joao Rito
joaovictor.rito@gmail.com
In reply to: Tomás Senart (#1)
Re: [PATCH v1] Match jsonpath variable names exactly when checking mutability

Hi Tomás,

Thank you for the patch. I may be wrong on some points, so please correct me.

I tested v1 on a current master (19ab0ba), built with --enable-cassert.

Without the patch, this statement is accepted:

CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js,
'$.datetime("HH:MI TZH") < $x'
PASSING '12:34'::timetz AS xx, '12:34'::time AS x));

With the patch, it fails with "functions in index expression must be marked IMMUTABLE". I think this is the expected result, because `$x` is a `time` value and the comparison depends on TimeZone.

The patch applies cleanly. `make check` passes (243 tests).

Some small notes, which may be only my taste:

1. The second new test (`$xx` with `AS x` and `AS xx`) gives the same error on master, so it does not fail without the fix. It still looks useful to me as a guard for the other direction. Maybe a short comment could say this.
2. In the new condition, `strlen(varname->sval) != len || strncmp(...) != 0`, I first wondered if `strcmp` would be simpler. I think `name` may not be NUL-terminated, so `len` is needed and your version looks fine to me. I did not check this in detail.
3. The commit message says previously accepted indexes may need to be dropped and that a dump restore can fail. I think this is a good thing to keep, since it is an upgrade note.

What I did not test:

- Your wrong-result example with different TimeZone settings (Tokyo vs UTC). I only tested that CREATE INDEX is rejected.
- `make installcheck`. I ran only `make check`.
- Other branches (the patch says backpatch to 17). I only tested master.
- The upgrade case: an index created on an older minor version, then pg_dump / restore on a patched server.
- Other jsonpath functions (JSON_EXISTS, JSON_VALUE, jsonb_path_* in index or generated column expressions).
- Variables with names that are prefixes in a longer list of PASSING items (3 or more names).
- Non-ASCII variable names and multibyte encodings.
- Performance (I expect no effect).
- Build with other compilers, Windows, or meson.

Thank you for your time.

Best regards,
Joao Rito

#3Tomás Senart
ts@perfloop.ai
In reply to: Joao Rito (#2)
Re: [PATCH v1] Match jsonpath variable names exactly when checking mutability

Hi Joao,

Thanks for testing the patch and for the careful notes.

1. Agreed. v2 adds a comment above the second test: it was rejected
before the fix too, and it guards the reverse match. The code and the
other tests do not change.
2. I checked this. The stored jsonpath string ends with a NUL byte
(flattenJsonPathParseItem() appends one), so strcmp() would also work.
I kept the length comparison because GetJsonPathVar() makes the same
check at execution time (namelen == varNameLen && strncmp(...)), so
the two places match.
3. Thanks, I kept the upgrade note.

I also ran the items you listed as not tested. All builds have
assertions enabled.

- Wrong result on unpatched master (4892c94): a table of 1000 rows
with js = '"08:00 +00"', and an index on JSON_QUERY(js,
'$.datetime("HH:MI TZH") < $x' PASSING '12:34+00'::timetz AS xx,
'12:34'::time AS x), built with TimeZone = 'UTC'. With UTC, the index
scan and the sequential scan both return 1000 rows. With Asia/Tokyo,
the index scan returns 1000 rows and the sequential scan returns 0.
With the patch, CREATE INDEX fails.
- make check and make installcheck pass on master (243 tests).
- REL_18_STABLE and REL_17_STABLE: the v1 expected output does not
apply there, because these branches do not print the "LINE 1:" context
for this error. The attached back-branch patch has the same code and
tests, with only the expected output changed. It applies to both
branches, and make check passes (235 tests on 18, 229 on 17).
- JSON_QUERY, JSON_VALUE and JSON_EXISTS, each in an index expression
and in a stored generated column, with three PASSING names where each
is a prefix of the next (x, xx, xxx): all 6 orders, the time value on
each name, and each name referenced, plus the two-name cases. That is
348 statements. With the patch, every statement is rejected exactly
when the referenced variable is the time value. On unpatched master,
96 of the 348 give the wrong answer.
- The same 348 statements with non-ASCII names, in a UTF8 database (é,
éé, ééé) and in an EUC_JP database (あ, ああ, あああ), give the same results
with the patch.
- jsonb_path_exists(), jsonb_path_query_first() and jsonb_path_match()
with a vars argument are rejected in index expressions with and
without the patch. The patch does not change them.

I did not test the upgrade case, performance, or other compilers,
Windows or meson.

Regards,
Tomás

Attachments:

v2-REL_17-0001-Match-jsonpath-variable-names-exactly-when-checki.patch.txttext/plain; charset=US-ASCII; name=v2-REL_17-0001-Match-jsonpath-variable-names-exactly-when-checki.patch.txtDownload+12-2
v2-0001-Match-jsonpath-variable-names-exactly-when-checki.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Match-jsonpath-variable-names-exactly-when-checki.patchDownload+16-2