v1-0001-handle-various-jsonpath-methods-in-jspI.v39_001_changes
application/octet-stream
Filename: v1-0001-handle-various-jsonpath-methods-in-jspI.v39_001_changes
Type: application/octet-stream
Part: 0
Message:
Re: remaining sql/json patches
From cd8b487dfca673f96bdd4cb3d7654afd20644fa4 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 5 Feb 2024 19:03:00 +0800
Subject: [PATCH v1 1/1] handle various jsonpath methods in jspIsMutableWalker
various jsonpath methods added in commit 66ea94e8e606529bb334515f388c62314956739e
so we need handle these methods in jspIsMutableWalker.
based on following query output, we handle the expression is mutable or not.
begin;
SET LOCAL TIME ZONE 10.5;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select JSON_QUERY(s, '$.timestamp_tz()')::text,'+10.5'::text, 'timestamp_tz'::text from cte
union all
select JSON_QUERY(s, '$.time()')::text,'+10.5'::text, 'time'::text from cte
union all
select JSON_QUERY(s, '$.timestamp()')::text,'+10.5'::text, 'timestamp'::text from cte
union all
select JSON_QUERY(s, '$.date()')::text,'+10.5'::text, 'date'::text from cte
union all
select JSON_QUERY(s, '$.time_tz()')::text,'+10.5'::text, 'time_tz'::text from cte;
SET LOCAL TIME ZONE -8;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select JSON_QUERY(s, '$.timestamp_tz()')::text,'+10.5'::text, 'timestamp_tz'::text from cte
union all
select JSON_QUERY(s, '$.time()')::text,'+10.5'::text, 'time'::text from cte
union all
select JSON_QUERY(s, '$.timestamp()')::text,'+10.5'::text, 'timestamp'::text from cte
union all
select JSON_QUERY(s, '$.date()')::text,'+10.5'::text, 'date'::text from cte
union all
select JSON_QUERY(s, '$.time_tz()')::text,'+10.5'::text, 'time_tz'::text from cte;
commit;
---
src/backend/utils/adt/jsonfuncs.c | 1 +
src/backend/utils/adt/jsonpath.c | 18 +++++++++
.../regress/expected/sqljson_queryfuncs.out | 40 +++++++++++++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 23 +++++++++++
4 files changed, 82 insertions(+)
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 54dbd7e7..9586bf23 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -3349,6 +3349,7 @@ json_populate_type(Datum json_val, Oid json_type,
jsv.val.json.str = NULL;
else
jsv.val.jsonb = NULL;
+ return (Datum) 0;
}
else if (jsv.is_json)
{
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 6ef914a5..25421d9b 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -1462,6 +1462,18 @@ jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
}
break;
+ case jpiTime:
+ case jpiDate:
+ case jpiTimestamp:
+ status = jpdsDateTimeNonZoned;
+ cxt->mutable = true;
+ break;
+
+ case jpiTimeTz:
+ case jpiTimestampTz:
+ status = jpdsDateTimeZoned;
+ cxt->mutable = true;
+ break;
case jpiLikeRegex:
Assert(status == jpdsNonDateTime);
jspInitByBuffer(&arg, jpi->base, jpi->content.like_regex.expr);
@@ -1487,6 +1499,12 @@ jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
case jpiCeiling:
case jpiDouble:
case jpiKeyValue:
+ case jpiBigint:
+ case jpiBoolean:
+ case jpiInteger:
+ case jpiNumber:
+ case jpiDecimal:
+ case jpiStringFunc:
status = jpdsNonDateTime;
break;
}
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index a3bf5c18..3684f2e7 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1110,6 +1110,46 @@ DROP TABLE test_jsonb_constraints;
CREATE TABLE test_jsonb_mutability(js jsonb, b int);
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.time()'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.date()'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.time_tz()'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.timestamp()'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.timestamp_tz()'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.date() < $.time_tz())'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.date() < $.time())'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.time() < $.time())'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.time() < $.time_tz())'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp() < $.timestamp_tz())'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp_tz() < $.timestamp_tz())'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.time() < $.datetime("HH:MI TZH"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.date() < $.datetime("HH:MI TZH"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp() < $.datetime("HH:MI TZH"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp() < $.datetime("HH:MI"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp_tz() < $.datetime("HH:MI TZH"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp_tz() < $.datetime("HH:MI"))'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.date() < $x' PASSING '12:34'::timetz AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.date() < $x' PASSING '1234'::int AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp(2) < $.timestamp(3))'));
+ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@ < $.datetime())'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime())'));
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index c3060e42..6e155fe6 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -349,6 +349,29 @@ DROP TABLE test_jsonb_constraints;
CREATE TABLE test_jsonb_mutability(js jsonb, b int);
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.time()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.date()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.time_tz()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.timestamp()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.timestamp_tz()'));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.date() < $.time_tz())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.date() < $.time())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.time() < $.time())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.time() < $.time_tz())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp() < $.timestamp_tz())'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp_tz() < $.timestamp_tz())'));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.time() < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.date() < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp() < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp() < $.datetime("HH:MI"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp_tz() < $.datetime("HH:MI TZH"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp_tz() < $.datetime("HH:MI"))'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.date() < $x' PASSING '12:34'::timetz AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.date() < $x' PASSING '1234'::int AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.timestamp(2) < $.timestamp(3))'));
+
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@ < $.datetime())'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime())'));
--
2.34.1