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
From 01b96f92bdfc1dbbcf3b6efc8aeafb820772bf4f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Tom=C3=A1s=20Senart?= <[email protected]>
Date: Tue, 10 Nov 2026 14:05:43 +0000
Subject: [PATCH v1] Match jsonpath variable names exactly when checking
mutability
jspIsMutableWalker() found the PASSING argument for a jsonpath variable
with strncmp() over the length of the variable's name only. A PASSING
argument whose name starts with the variable's name, such as "aa" for
$a, could therefore supply the datatype used to decide whether a
datetime comparison depends on the TimeZone setting. GetJsonPathVar()
already requires an exact name match at execution time, so the
mutability check could disagree with execution.
As a result, CREATE INDEX could accept an index expression whose result
depends on TimeZone, and could reject one that is immutable.
Also require equal name lengths, as GetJsonPathVar() does. Add
regression tests for a PASSING name that extends the variable's name
and for one that is a prefix of it.
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.
Backpatch-through: 17
---
src/backend/utils/adt/jsonpath.c | 3 ++-
src/test/regress/expected/sqljson_queryfuncs.out | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 3 +++
3 files changed, 14 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 7bfc18c..a016565 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -1447,7 +1447,8 @@ jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
String *varname = lfirst_node(String, lc1);
Node *varexpr = lfirst(lc2);
- if (strncmp(varname->sval, name, len))
+ if (strlen(varname->sval) != len ||
+ strncmp(varname->sval, name, len) != 0)
continue;
switch (exprType(varexpr))
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index c0086de..5322a0f 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1343,6 +1343,15 @@ LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ?...
^
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $x' PASSING '12:34'::timetz AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $y' PASSING '12:34'::timetz AS x));
+-- PASSING values whose names extend or prefix the variable's name are not used
+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));
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.dat...
+ ^
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $xx' PASSING '12:34'::timetz AS x, '12:34'::time AS xx));
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.dat...
+ ^
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '12:34'::timetz AS x));
ERROR: functions in index expression must be marked IMMUTABLE
LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.dat...
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index d5ea5b3..9a84270 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -430,6 +430,9 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI"
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("YY-MM-DD HH:MI"))'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $x' PASSING '12:34'::timetz AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $y' PASSING '12:34'::timetz AS x));
+-- PASSING values whose names extend or prefix the variable's name are not used
+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));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $xx' PASSING '12:34'::timetz AS x, '12:34'::time AS xx));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '12:34'::timetz AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '1234'::int AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() ? (@ == $x)' PASSING '12:34'::time AS x));
--
2.55.0
