pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Jonathan S. Katz <jkatz@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: John Naylor <john.naylor@enterprisedb.com>
Subject: Re: SQL/JSON features for v15
Date: Tue, 30 Aug 2022 00:49:08 +0300
Message-ID: <c3b315b6-1e9f-6aa4-8708-daa19cf3f1a3@postgrespro.ru> (raw )
In-Reply-To: <CA+HiwqHjr0UE2=OR14DWE=ykrjvLyZMvX8xFDt-GDvWm3AuMzw@mail.gmail.com >
References: <abd9b83b-aa66-f230-3d6d-734817f0995d@postgresql.org >
<7d83684b-7932-9f29-400b-0beedfafcdd4@postgrespro.ru >
<69022273-e7e3-6d1a-10f8-d1b4740e4b97@postgresql.org >
<92cab50c-152b-84fc-fd49-12b936113a3d@postgresql.org >
<CA+TgmoaAZq6rRdVA65PBXBKs=jUHhf42Jf78kBLOaX3c6J7MfQ@mail.gmail.com >
<692364.1661267311@sss.pgh.pa.us >
<35468585-e18f-3be6-a228-4ec478f796ac@postgresql.org >
<716936.1661275451@sss.pgh.pa.us >
<f1c79751-2916-a9cf-c8b9-668b9700edb8@dunslane.net >
<ab10b5df-fcc2-5871-7027-e7f73b11559d@postgresql.org >
<3596205e-ce1e-6959-7559-6ddd67f8294b@postgrespro.ru >
<cc17d718-56a1-359f-2792-01c5debd67e3@dunslane.net >
<3f8d4c2b-1e58-bf19-580e-64e9755897ef@postgrespro.ru >
<072a47f0-7a39-2266-5f90-1178a3860be1@dunslane.net >
<1992b190-f00b-8435-b561-e3c6ac450482@postgresql.org >
<59660a0b-7b7a-75ab-75c4-b6d53fbda5f6@dunslane.net >
<6ba7ba7b-9fdd-674f-c227-59a59e0de1ec@postgrespro.ru >
<CA+HiwqHjr0UE2=OR14DWE=ykrjvLyZMvX8xFDt-GDvWm3AuMzw@mail.gmail.com >
Hi,
On 29.08.2022 15:56, Amit Langote wrote:
> On Sat, Aug 27, 2022 at 5:11 AM Nikita Glukhov<n.gluhov@postgrespro.ru> wrote:
>> On 26.08.2022 22:25, Andrew Dunstan wrote:
>>
>> On 2022-08-24 We 20:05, Nikita Glukhov wrote:
>>
>> I have completed in v9 all the things I previously planned:
>>
>> - Added missing safe I/O and type conversion functions for
>> datetime, float4, varchar, bpchar. This introduces a lot
>> of boilerplate code for returning errors and also maybe
>> adds some overhead.
> Didn't know that we have done similar things in the past for jsonpath, as in:
>
> commit 16d489b0fe058e527619f5e9d92fd7ca3c6c2994
> Author: Alexander Korotkov<akorotkov@postgresql.org>
> Date: Sat Mar 16 12:21:19 2019 +0300
>
> Numeric error suppression in jsonpath
This was necessary for handling errors in arithmetic operations.
> BTW, maybe the following hunk in boolin_opt_error() is unnecessary?
>
> - len = strlen(str);
> + len -= str - in_str;
>
This is really not necessary, but helps to avoid extra strlen() call.
I have replaced it with more intuitive
+ {
str++;
+ len--;
+ }
- len = strlen(str);
>> - Added JSON_QUERY coercion to UTF8 bytea using pg_convert_to().
>>
>> - Added immutability checks that were missed with elimination
>> of coercion expressions.
>> Coercions text::datetime, datetime1::datetime2 and even
>> datetime::text for some datetime types are mutable.
>> datetime::text can be made immutable by passing ISO date
>> style into output functions (like in jsonpath).
>>
>> - Disabled non-Const expressions in DEFAULT ON EMPTY in non
>> ERROR ON ERROR case. Non-constant expressions are tried to
>> evaluate into Const directly inside transformExpr().
> I am not sure if it's OK to eval_const_expressions() on a Query
> sub-expression during parse-analysis. IIUC, it is only correct to
> apply it to after the rewriting phase.
I also was not sure. Maybe it can be moved to rewriting phase or
even to execution phase.
>> Maybe it would be better to simply remove DEFAULT ON EMPTY.
> So +1 to this for now.
See last patch #9.
>> It is possible to easily split this patch into several subpatches,
>> I will do it if needed.
> That would be nice indeed.
I have extracted patches #1-6 with numerous safe input and type conversion
functions.
> I'm wondering if you're going to change the PASSING values
> initialization to add the steps into the parent JsonExpr's ExprState,
> like the previous patch was doing?
I forget to incorporate your changes for subsidary ExprStates elimination.
See patch #8.
--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
Attachments: download all (9 files, .tar.gz)
v9-0001-Add-safe-input-function-for-bool.patch (text/x-patch, 2.3K) download
From ffeadb09e8ab55b5a677716c6f7ebdafba778d38 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Mon, 29 Aug 2022 23:53:06 +0300
Subject: [PATCH v9 1/9] Add safe input function for bool
---
src/backend/utils/adt/bool.c | 33 +++++++++++++++++++++------------
src/include/utils/builtins.h | 1 +
2 files changed, 22 insertions(+), 12 deletions(-)
diff --git a/src/backend/utils/adt/bool.c b/src/backend/utils/adt/bool.c
index cd7335287f9..2494f441285 100644
--- a/src/backend/utils/adt/bool.c
+++ b/src/backend/utils/adt/bool.c
@@ -126,12 +126,10 @@ parse_bool_with_len(const char *value, size_t len, bool *result)
*
* In the switch statement, check the most-used possibilities first.
*/
-Datum
-boolin(PG_FUNCTION_ARGS)
+bool
+boolin_opt_error(const char *in_str, size_t len, bool *error)
{
- const char *in_str = PG_GETARG_CSTRING(0);
const char *str;
- size_t len;
bool result;
/*
@@ -139,22 +137,33 @@ boolin(PG_FUNCTION_ARGS)
*/
str = in_str;
while (isspace((unsigned char) *str))
+ {
str++;
+ len--;
+ }
- len = strlen(str);
while (len > 0 && isspace((unsigned char) str[len - 1]))
len--;
if (parse_bool_with_len(str, len, &result))
- PG_RETURN_BOOL(result);
+ return result;
+
+ if (!error)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "boolean", in_str)));
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "boolean", in_str)));
+ *error = true;
+ return false;
+}
+
+Datum
+boolin(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
- /* not reached */
- PG_RETURN_BOOL(false);
+ PG_RETURN_BOOL(boolin_opt_error(str, strlen(str), NULL));
}
/*
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 221c3e6c3de..2c03ce4fe12 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -24,6 +24,7 @@
/* bool.c */
extern bool parse_bool(const char *value, bool *result);
extern bool parse_bool_with_len(const char *value, size_t len, bool *result);
+extern bool boolin_opt_error(const char *value, size_t len, bool *error);
/* domains.c */
extern void domain_check(Datum value, bool isnull, Oid domainType,
--
2.17.1
v9-0002-Add-safe-input-function-for-int8.patch (text/x-patch, 2.1K) download
From 7a0f71e430136a3bcec95919149b2a1e1d6e86f5 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Tue, 30 Aug 2022 00:13:54 +0300
Subject: [PATCH v9 2/9] Add safe input function for int8
---
src/backend/utils/adt/numutils.c | 20 +++++++++++++++++++-
src/include/utils/builtins.h | 1 +
2 files changed, 20 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index cc3f95d3990..b82b30f9013 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -248,7 +248,7 @@ invalid_syntax:
* positive number.
*/
int64
-pg_strtoint64(const char *s)
+pg_strtoint64_opt_error(const char *s, bool *error)
{
const char *ptr = s;
int64 tmp = 0;
@@ -307,12 +307,24 @@ pg_strtoint64(const char *s)
return tmp;
out_of_range:
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("value \"%s\" is out of range for type %s",
s, "bigint")));
invalid_syntax:
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type %s: \"%s\"",
@@ -321,6 +333,12 @@ invalid_syntax:
return 0; /* keep compiler quiet */
}
+int64
+pg_strtoint64(const char *s)
+{
+ return pg_strtoint64_opt_error(s, NULL);
+}
+
/*
* pg_itoa: converts a signed 16-bit integer to its string representation
* and returns strlen(a).
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 2c03ce4fe12..5e1aa37c1f5 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -47,6 +47,7 @@ extern int namestrcmp(Name name, const char *str);
extern int16 pg_strtoint16(const char *s);
extern int32 pg_strtoint32(const char *s);
extern int64 pg_strtoint64(const char *s);
+extern int64 pg_strtoint64_opt_error(const char *s, bool *error);
extern int pg_itoa(int16 i, char *a);
extern int pg_ultoa_n(uint32 l, char *a);
extern int pg_ulltoa_n(uint64 l, char *a);
--
2.17.1
v9-0003-Add-safe-input-function-for-float4.patch (text/x-patch, 4.3K) download
From 9f4179494ef4612eadd8c2c6787391d85347fa4d Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Tue, 30 Aug 2022 00:13:36 +0300
Subject: [PATCH v9 3/9] Add safe input function for float4
---
src/backend/utils/adt/float.c | 70 +++++++++++++++++++----------------
src/include/utils/float.h | 1 +
2 files changed, 40 insertions(+), 31 deletions(-)
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index fc8f39a7a98..d9da8c8466d 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -73,6 +73,16 @@ static double sind_q1(double x);
static double cosd_q1(double x);
static void init_degree_constants(void);
+/* Convenience macro: set *have_error flag (if provided) or throw error */
+#define RETURN_ERROR(throw_error, have_error) \
+do { \
+ if (have_error) { \
+ *have_error = true; \
+ return 0.0; \
+ } else { \
+ throw_error; \
+ } \
+} while (0)
/*
* We use these out-of-line ereport() calls to report float overflow,
@@ -159,10 +169,9 @@ is_infinite(double val)
* result of 0xAE43FEp-107.
*
*/
-Datum
-float4in(PG_FUNCTION_ARGS)
+float
+float4in_opt_error(char *num, bool *have_error)
{
- char *num = PG_GETARG_CSTRING(0);
char *orig_num;
float val;
char *endptr;
@@ -183,10 +192,11 @@ float4in(PG_FUNCTION_ARGS)
* strtod() on different platforms.
*/
if (*num == '\0')
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "real", orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "real", orig_num))),
+ have_error);
errno = 0;
val = strtof(num, &endptr);
@@ -257,16 +267,18 @@ float4in(PG_FUNCTION_ARGS)
(val >= HUGE_VALF || val <= -HUGE_VALF)
#endif
)
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("\"%s\" is out of range for type real",
- orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("\"%s\" is out of range for type real",
+ orig_num))),
+ have_error);
}
else
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "real", orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "real", orig_num))),
+ have_error);
}
/* skip trailing whitespace */
@@ -275,12 +287,19 @@ float4in(PG_FUNCTION_ARGS)
/* if there is any junk left at the end of the string, bail out */
if (*endptr != '\0')
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "real", orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "real", orig_num))),
+ have_error);
+
+ return val;
+}
- PG_RETURN_FLOAT4(val);
+Datum
+float4in(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_FLOAT4(float4in_opt_error(PG_GETARG_CSTRING(0), NULL));
}
/*
@@ -340,17 +359,6 @@ float8in(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(float8in_internal(num, NULL, "double precision", num));
}
-/* Convenience macro: set *have_error flag (if provided) or throw error */
-#define RETURN_ERROR(throw_error, have_error) \
-do { \
- if (have_error) { \
- *have_error = true; \
- return 0.0; \
- } else { \
- throw_error; \
- } \
-} while (0)
-
/*
* float8in_internal_opt_error - guts of float8in()
*
diff --git a/src/include/utils/float.h b/src/include/utils/float.h
index 4bf0e3ac07a..0ad4d9f3973 100644
--- a/src/include/utils/float.h
+++ b/src/include/utils/float.h
@@ -46,6 +46,7 @@ extern float8 float8in_internal(char *num, char **endptr_p,
extern float8 float8in_internal_opt_error(char *num, char **endptr_p,
const char *type_name, const char *orig_string,
bool *have_error);
+extern float float4in_opt_error(char *num, bool *have_error);
extern char *float8out_internal(float8 num);
extern int float4_cmp_internal(float4 a, float4 b);
extern int float8_cmp_internal(float8 a, float8 b);
--
2.17.1
v9-0004-Add-safe-input-and-type-conversion-functions-for-.patch (text/x-patch, 13.7K) download
From 8365b41223945a3cfd9615fa33488d774f00e0f8 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Mon, 29 Aug 2022 23:55:52 +0300
Subject: [PATCH v9 4/9] Add safe input and type conversion functions for
numeric
---
src/backend/utils/adt/numeric.c | 230 ++++++++++++++++++++++----------
src/include/utils/numeric.h | 6 +-
2 files changed, 161 insertions(+), 75 deletions(-)
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 920a63b0081..951b2f43ab1 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -498,7 +498,7 @@ static void free_var(NumericVar *var);
static void zero_var(NumericVar *var);
static const char *set_var_from_str(const char *str, const char *cp,
- NumericVar *dest);
+ NumericVar *dest, bool *have_error);
static void set_var_from_num(Numeric value, NumericVar *dest);
static void init_var_from_num(Numeric num, NumericVar *dest);
static void set_var_from_var(const NumericVar *value, NumericVar *dest);
@@ -512,8 +512,8 @@ static Numeric duplicate_numeric(Numeric num);
static Numeric make_result(const NumericVar *var);
static Numeric make_result_opt_error(const NumericVar *var, bool *error);
-static void apply_typmod(NumericVar *var, int32 typmod);
-static void apply_typmod_special(Numeric num, int32 typmod);
+static bool apply_typmod(NumericVar *var, int32 typmod, bool *error);
+static void apply_typmod_special(Numeric num, int32 typmod, bool *error);
static bool numericvar_to_int32(const NumericVar *var, int32 *result);
static bool numericvar_to_int64(const NumericVar *var, int64 *result);
@@ -607,21 +607,25 @@ static void accum_sum_combine(NumericSumAccum *accum, NumericSumAccum *accum2);
* ----------------------------------------------------------------------
*/
+/* Convenience macro: set *have_error flag (if provided) or throw error */
+#define RETURN_ERROR(throw_error, have_error) \
+do { \
+ if (have_error) { \
+ *(have_error) = true; \
+ return NULL; \
+ } else { \
+ throw_error; \
+ } \
+} while (0)
/*
* numeric_in() -
*
* Input function for numeric data type
*/
-Datum
-numeric_in(PG_FUNCTION_ARGS)
+Numeric
+numeric_in_opt_error(char *str, int32 typmod, bool *have_error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
Numeric res;
const char *cp;
@@ -682,7 +686,10 @@ numeric_in(PG_FUNCTION_ARGS)
init_var(&value);
- cp = set_var_from_str(str, cp, &value);
+ cp = set_var_from_str(str, cp, &value, have_error);
+
+ if (!cp)
+ return NULL; /* error */
/*
* We duplicate a few lines of code here because we would like to
@@ -693,38 +700,59 @@ numeric_in(PG_FUNCTION_ARGS)
while (*cp)
{
if (!isspace((unsigned char) *cp))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
cp++;
}
- apply_typmod(&value, typmod);
+ if (!apply_typmod(&value, typmod, have_error))
+ return NULL; /* error */
- res = make_result(&value);
+ res = make_result_opt_error(&value, have_error);
free_var(&value);
- PG_RETURN_NUMERIC(res);
+ return res;
}
/* Should be nothing left but spaces */
while (*cp)
{
if (!isspace((unsigned char) *cp))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
+
cp++;
}
/* As above, throw any typmod error after finishing syntax check */
- apply_typmod_special(res, typmod);
+ apply_typmod_special(res, typmod, have_error);
- PG_RETURN_NUMERIC(res);
+ return res;
}
+/*
+ * numeric_in() -
+ *
+ * Input function for numeric data type
+ */
+Datum
+numeric_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_NUMERIC(numeric_in_opt_error(str, typmod, NULL));
+}
/*
* numeric_out() -
@@ -1058,7 +1086,7 @@ numeric_recv(PG_FUNCTION_ARGS)
{
trunc_var(&value, value.dscale);
- apply_typmod(&value, typmod);
+ apply_typmod(&value, typmod, NULL);
res = make_result(&value);
}
@@ -1067,7 +1095,7 @@ numeric_recv(PG_FUNCTION_ARGS)
/* apply_typmod_special wants us to make the Numeric first */
res = make_result(&value);
- apply_typmod_special(res, typmod);
+ apply_typmod_special(res, typmod, NULL);
}
free_var(&value);
@@ -1180,7 +1208,7 @@ numeric (PG_FUNCTION_ARGS)
*/
if (NUMERIC_IS_SPECIAL(num))
{
- apply_typmod_special(num, typmod);
+ apply_typmod_special(num, typmod, NULL);
PG_RETURN_NUMERIC(duplicate_numeric(num));
}
@@ -1231,7 +1259,7 @@ numeric (PG_FUNCTION_ARGS)
init_var(&var);
set_var_from_num(num, &var);
- apply_typmod(&var, typmod);
+ apply_typmod(&var, typmod, NULL);
new = make_result(&var);
free_var(&var);
@@ -4308,15 +4336,20 @@ int8_numeric(PG_FUNCTION_ARGS)
}
-Datum
-numeric_int8(PG_FUNCTION_ARGS)
+int64
+numeric_int8_opt_error(Numeric num, bool *error)
{
- Numeric num = PG_GETARG_NUMERIC(0);
NumericVar x;
int64 result;
if (NUMERIC_IS_SPECIAL(num))
{
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
if (NUMERIC_IS_NAN(num))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4331,13 +4364,26 @@ numeric_int8(PG_FUNCTION_ARGS)
init_var_from_num(num, &x);
if (!numericvar_to_int64(&x, &result))
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("bigint out of range")));
+ }
- PG_RETURN_INT64(result);
+ return result;
}
+Datum
+numeric_int8(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(numeric_int8_opt_error(PG_GETARG_NUMERIC(0), NULL));
+}
Datum
int2_numeric(PG_FUNCTION_ARGS)
@@ -4347,17 +4393,20 @@ int2_numeric(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(int64_to_numeric(val));
}
-
-Datum
-numeric_int2(PG_FUNCTION_ARGS)
+int16
+numeric_int2_opt_error(Numeric num, bool *error)
{
- Numeric num = PG_GETARG_NUMERIC(0);
NumericVar x;
int64 val;
- int16 result;
if (NUMERIC_IS_SPECIAL(num))
{
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
if (NUMERIC_IS_NAN(num))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4372,21 +4421,40 @@ numeric_int2(PG_FUNCTION_ARGS)
init_var_from_num(num, &x);
if (!numericvar_to_int64(&x, &val))
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("smallint out of range")));
+ }
if (unlikely(val < PG_INT16_MIN) || unlikely(val > PG_INT16_MAX))
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("smallint out of range")));
+ }
/* Down-convert to int2 */
- result = (int16) val;
-
- PG_RETURN_INT16(result);
+ return (int16) val;
}
+Datum
+numeric_int2(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT16(numeric_int2_opt_error(PG_GETARG_NUMERIC(0), NULL));
+}
Datum
float8_numeric(PG_FUNCTION_ARGS)
@@ -4412,7 +4480,7 @@ float8_numeric(PG_FUNCTION_ARGS)
init_var(&result);
/* Assume we need not worry about leading/trailing spaces */
- (void) set_var_from_str(buf, buf, &result);
+ (void) set_var_from_str(buf, buf, &result, NULL);
res = make_result(&result);
@@ -4505,7 +4573,7 @@ float4_numeric(PG_FUNCTION_ARGS)
init_var(&result);
/* Assume we need not worry about leading/trailing spaces */
- (void) set_var_from_str(buf, buf, &result);
+ (void) set_var_from_str(buf, buf, &result, NULL);
res = make_result(&result);
@@ -6811,7 +6879,8 @@ zero_var(NumericVar *var)
* reports. (Typically cp would be the same except advanced over spaces.)
*/
static const char *
-set_var_from_str(const char *str, const char *cp, NumericVar *dest)
+set_var_from_str(const char *str, const char *cp, NumericVar *dest,
+ bool *have_error)
{
bool have_dp = false;
int i;
@@ -6849,10 +6918,11 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
}
if (!isdigit((unsigned char) *cp))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
decdigits = (unsigned char *) palloc(strlen(cp) + DEC_DIGITS * 2);
@@ -6873,10 +6943,11 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
else if (*cp == '.')
{
if (have_dp)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
have_dp = true;
cp++;
}
@@ -6897,10 +6968,11 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
cp++;
exponent = strtol(cp, &endptr, 10);
if (endptr == cp)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
cp = endptr;
/*
@@ -6912,9 +6984,10 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
* for consistency use the same ereport errcode/text as make_result().
*/
if (exponent >= INT_MAX / 2 || exponent <= -(INT_MAX / 2))
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("value overflows numeric format")));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("value overflows numeric format"))),
+ have_error);
dweight += (int) exponent;
dscale -= (int) exponent;
if (dscale < 0)
@@ -7420,17 +7493,10 @@ make_result_opt_error(const NumericVar *var, bool *have_error)
if (NUMERIC_WEIGHT(result) != weight ||
NUMERIC_DSCALE(result) != var->dscale)
{
- if (have_error)
- {
- *have_error = true;
- return NULL;
- }
- else
- {
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("value overflows numeric format")));
- }
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("value overflows numeric format"))),
+ have_error);
}
dump_numeric("make_result()", result);
@@ -7456,8 +7522,8 @@ make_result(const NumericVar *var)
* Do bounds checking and rounding according to the specified typmod.
* Note that this is only applied to normal finite values.
*/
-static void
-apply_typmod(NumericVar *var, int32 typmod)
+static bool
+apply_typmod(NumericVar *var, int32 typmod, bool *have_error)
{
int precision;
int scale;
@@ -7467,7 +7533,7 @@ apply_typmod(NumericVar *var, int32 typmod)
/* Do nothing if we have an invalid typmod */
if (!is_valid_numeric_typmod(typmod))
- return;
+ return true;
precision = numeric_typmod_precision(typmod);
scale = numeric_typmod_scale(typmod);
@@ -7514,6 +7580,13 @@ apply_typmod(NumericVar *var, int32 typmod)
#error unsupported NBASE
#endif
if (ddigits > maxdigits)
+ {
+ if (have_error)
+ {
+ *have_error = true;
+ return false;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
@@ -7523,11 +7596,14 @@ apply_typmod(NumericVar *var, int32 typmod)
maxdigits ? "10^" : "",
maxdigits ? maxdigits : 1
)));
+ }
break;
}
ddigits -= DEC_DIGITS;
}
}
+
+ return true;
}
/*
@@ -7537,7 +7613,7 @@ apply_typmod(NumericVar *var, int32 typmod)
* For convenience of most callers, the value is presented in packed form.
*/
static void
-apply_typmod_special(Numeric num, int32 typmod)
+apply_typmod_special(Numeric num, int32 typmod, bool *have_error)
{
int precision;
int scale;
@@ -7560,6 +7636,12 @@ apply_typmod_special(Numeric num, int32 typmod)
precision = numeric_typmod_precision(typmod);
scale = numeric_typmod_scale(typmod);
+ if (have_error)
+ {
+ *have_error = true;
+ return;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 3caa74dfe7a..f32807cfdaa 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -85,6 +85,10 @@ extern Numeric numeric_div_opt_error(Numeric num1, Numeric num2,
bool *have_error);
extern Numeric numeric_mod_opt_error(Numeric num1, Numeric num2,
bool *have_error);
-extern int32 numeric_int4_opt_error(Numeric num, bool *error);
+extern int16 numeric_int2_opt_error(Numeric num, bool *have_error);
+extern int32 numeric_int4_opt_error(Numeric num, bool *have_error);
+extern int64 numeric_int8_opt_error(Numeric num, bool *have_error);
+extern Numeric numeric_in_opt_error(char *str, int32 typmod,
+ bool *have_error);
#endif /* _PG_NUMERIC_H_ */
--
2.17.1
v9-0005-Add-safe-input-and-type-conversion-functions-for-.patch (text/x-patch, 22.5K) download
From 73dd55775b4ed2609b6b052046423db6241e6a54 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Mon, 29 Aug 2022 23:54:45 +0300
Subject: [PATCH v9 5/9] Add safe input and type conversion functions for
datetime types
---
contrib/adminpack/adminpack.c | 2 +-
src/backend/utils/adt/date.c | 277 ++++++++++++++++++++++++------
src/backend/utils/adt/datetime.c | 16 +-
src/backend/utils/adt/timestamp.c | 126 +++++++++++---
src/backend/utils/misc/guc.c | 2 +-
src/include/utils/date.h | 13 ++
src/include/utils/datetime.h | 6 +-
src/include/utils/timestamp.h | 6 +
8 files changed, 363 insertions(+), 85 deletions(-)
diff --git a/contrib/adminpack/adminpack.c b/contrib/adminpack/adminpack.c
index 03addf1dc5f..4bf795d42a4 100644
--- a/contrib/adminpack/adminpack.c
+++ b/contrib/adminpack/adminpack.c
@@ -571,7 +571,7 @@ pg_logdir_ls_internal(FunctionCallInfo fcinfo)
if (ParseDateTime(timestampbuf, lowstr, MAXDATELEN, field, ftype, MAXDATEFIELDS, &nf))
continue;
- if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz))
+ if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz, true))
continue;
/* Seems the timestamp is OK; prepare and return tuple */
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 081dfa2450f..43a96fc5142 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -108,10 +108,9 @@ anytime_typmodout(bool istz, int32 typmod)
/* date_in()
* Given date text string, convert to internal date format.
*/
-Datum
-date_in(PG_FUNCTION_ARGS)
+DateADT
+date_in_opt_error(char *str, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
DateADT date;
fsec_t fsec;
struct pg_tm tt,
@@ -127,9 +126,18 @@ date_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "date");
+ }
switch (dtype)
{
@@ -149,25 +157,56 @@ date_in(PG_FUNCTION_ARGS)
PG_RETURN_DATEADT(date);
default:
+ if (*error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(DTERR_BAD_FORMAT, str, "date");
break;
}
/* Prevent overflow in Julian-day routines */
if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("date out of range: \"%s\"", str)));
+ }
date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
/* Now check for just-out-of-range dates */
if (!IS_VALID_DATE(date))
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("date out of range: \"%s\"", str)));
+ }
- PG_RETURN_DATEADT(date);
+ return date;
+}
+
+/* date_in()
+ * Given date text string, convert to internal date format.
+ */
+Datum
+date_in(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_DATEADT(date_in_opt_error(PG_GETARG_CSTRING(0), NULL));
}
/* date_out()
@@ -1286,10 +1325,9 @@ date_timestamp(PG_FUNCTION_ARGS)
/* timestamp_date()
* Convert timestamp to date data type.
*/
-Datum
-timestamp_date(PG_FUNCTION_ARGS)
+DateADT
+timestamp_date_opt_error(Timestamp timestamp, bool *error)
{
- Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
DateADT result;
struct pg_tm tt,
*tm = &tt;
@@ -1302,16 +1340,32 @@ timestamp_date(PG_FUNCTION_ARGS)
else
{
if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
}
- PG_RETURN_DATEADT(result);
+ return result;
}
+/* timestamp_date()
+ * Convert timestamp to date data type.
+ */
+Datum
+timestamp_date(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_DATEADT(timestamp_date_opt_error(PG_GETARG_TIMESTAMP(0), NULL));
+}
/* date_timestamptz()
* Convert date to timestamp with time zone data type.
@@ -1327,14 +1381,9 @@ date_timestamptz(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMP(result);
}
-
-/* timestamptz_date()
- * Convert timestamp with time zone to date data type.
- */
-Datum
-timestamptz_date(PG_FUNCTION_ARGS)
+DateADT
+timestamptz_date_opt_error(TimestampTz timestamp, bool *error)
{
- TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
DateADT result;
struct pg_tm tt,
*tm = &tt;
@@ -1348,14 +1397,31 @@ timestamptz_date(PG_FUNCTION_ARGS)
else
{
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
}
- PG_RETURN_DATEADT(result);
+ return result;
+}
+
+/* timestamptz_date()
+ * Convert timestamp with time zone to date data type.
+ */
+Datum
+timestamptz_date(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_DATEADT(timestamptz_date_opt_error(PG_GETARG_TIMESTAMP(0), NULL));
}
@@ -1363,15 +1429,9 @@ timestamptz_date(PG_FUNCTION_ARGS)
* Time ADT
*****************************************************************************/
-Datum
-time_in(PG_FUNCTION_ARGS)
+TimeADT
+time_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
TimeADT result;
fsec_t fsec;
struct pg_tm tt,
@@ -1387,14 +1447,35 @@ time_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "time");
+ }
tm2time(tm, fsec, &result);
AdjustTimeForTypmod(&result, typmod);
- PG_RETURN_TIMEADT(result);
+ return result;
+}
+
+Datum
+time_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMEADT(time_in_opt_error(str, typmod, NULL));
}
/* tm2time()
@@ -1889,22 +1970,32 @@ overlaps_time(PG_FUNCTION_ARGS)
/* timestamp_time()
* Convert timestamp to time data type.
*/
-Datum
-timestamp_time(PG_FUNCTION_ARGS)
+TimeADT
+timestamp_time_opt_error(Timestamp timestamp, bool *isnull, bool *error)
{
- Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
TimeADT result;
struct pg_tm tt,
*tm = &tt;
fsec_t fsec;
if (TIMESTAMP_NOT_FINITE(timestamp))
- PG_RETURN_NULL();
+ {
+ *isnull = true;
+ return 0;
+ }
if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (error)
+ {
+ *error = *isnull = true;
+ return (Datum) 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
/*
* Could also do this with time = (timestamp / USECS_PER_DAY *
@@ -1912,17 +2003,33 @@ timestamp_time(PG_FUNCTION_ARGS)
*/
result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
USECS_PER_SEC) + fsec;
+ *isnull = false;
- PG_RETURN_TIMEADT(result);
+ return result;
}
-/* timestamptz_time()
- * Convert timestamptz to time data type.
+/* timestamp_time()
+ * Convert timestamp to time data type.
*/
Datum
-timestamptz_time(PG_FUNCTION_ARGS)
+timestamp_time(PG_FUNCTION_ARGS)
+{
+ bool isnull;
+ TimeADT time =
+ timestamp_time_opt_error(PG_GETARG_TIMESTAMP(0), &isnull, NULL);
+
+ if (isnull)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMEADT(time);
+}
+
+/* timestamptz_time_opt_error()
+ * Convert timestamptz to time data type.
+ */
+TimeADT
+timestamptz_time_opt_error(TimestampTz timestamp, bool *isnull, bool *error)
{
- TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
TimeADT result;
struct pg_tm tt,
*tm = &tt;
@@ -1930,12 +2037,23 @@ timestamptz_time(PG_FUNCTION_ARGS)
fsec_t fsec;
if (TIMESTAMP_NOT_FINITE(timestamp))
- PG_RETURN_NULL();
+ {
+ *isnull = true;
+ return 0;
+ }
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (error)
+ {
+ *error = *isnull = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
/*
* Could also do this with time = (timestamp / USECS_PER_DAY *
@@ -1943,8 +2061,25 @@ timestamptz_time(PG_FUNCTION_ARGS)
*/
result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
USECS_PER_SEC) + fsec;
+ *isnull = false;
- PG_RETURN_TIMEADT(result);
+ return result;
+}
+
+/* timestamptz_time()
+ * Convert timestamptz to time data type.
+ */
+Datum
+timestamptz_time(PG_FUNCTION_ARGS)
+{
+ bool isnull;
+ TimeADT result =
+ timestamptz_time_opt_error(PG_GETARG_TIMESTAMP(0), &isnull, NULL);
+
+ if (isnull)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMEADT(result);
}
/* datetime_timestamp()
@@ -2249,15 +2384,9 @@ tm2timetz(struct pg_tm *tm, fsec_t fsec, int tz, TimeTzADT *result)
return 0;
}
-Datum
-timetz_in(PG_FUNCTION_ARGS)
+TimeTzADT *
+timetz_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
TimeTzADT *result;
fsec_t fsec;
struct pg_tm tt,
@@ -2273,17 +2402,38 @@ timetz_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return NULL;
+ }
+
DateTimeParseError(dterr, str, "time with time zone");
+ }
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
tm2timetz(tm, fsec, tz, result);
AdjustTimeForTypmod(&(result->time), typmod);
- PG_RETURN_TIMETZADT_P(result);
+ return result;
}
+Datum
+timetz_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMETZADT_P(timetz_in_opt_error(str, typmod, NULL));
+}
Datum
timetz_out(PG_FUNCTION_ARGS)
{
@@ -2812,10 +2962,9 @@ time_timetz(PG_FUNCTION_ARGS)
/* timestamptz_timetz()
* Convert timestamp to timetz data type.
*/
-Datum
-timestamptz_timetz(PG_FUNCTION_ARGS)
+TimeTzADT *
+timestamptz_timetz_opt_error(TimestampTz timestamp, bool *error)
{
- TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
TimeTzADT *result;
struct pg_tm tt,
*tm = &tt;
@@ -2823,20 +2972,42 @@ timestamptz_timetz(PG_FUNCTION_ARGS)
fsec_t fsec;
if (TIMESTAMP_NOT_FINITE(timestamp))
- PG_RETURN_NULL();
+ return NULL;
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return NULL;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
tm2timetz(tm, fsec, tz, result);
- PG_RETURN_TIMETZADT_P(result);
+ return result;
}
+/* timestamptz_timetz()
+ * Convert timestamp to timetz data type.
+ */
+Datum
+timestamptz_timetz(PG_FUNCTION_ARGS)
+{
+ TimeTzADT *result =
+ timestamptz_timetz_opt_error(PG_GETARG_TIMESTAMP(0), NULL);
+
+ if (result)
+ PG_RETURN_TIMETZADT_P(result);
+ else
+ PG_RETURN_NULL();
+}
/* datetimetz_timestamptz()
* Convert date and timetz to timestamp with time zone data type.
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 43fff50d490..3275e5fd948 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -971,7 +971,8 @@ ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
*/
int
DecodeDateTime(char **field, int *ftype, int nf,
- int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp)
+ int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors)
{
int fmask = 0,
tmask,
@@ -1114,8 +1115,13 @@ DecodeDateTime(char **field, int *ftype, int nf,
/*
* We should return an error code instead of
* ereport'ing directly, but then there is no way
- * to report the bad time zone name.
+ * to report the bad time zone name. But
+ * throwing errors is disallowed, simply
+ * return error code.
*/
+ if (!throw_errors)
+ return DTERR_BAD_FORMAT;
+
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("time zone \"%s\" not recognized",
@@ -1921,7 +1927,8 @@ DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t, const char *abbr, pg_tz *tzp,
*/
int
DecodeTimeOnly(char **field, int *ftype, int nf,
- int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp)
+ int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors)
{
int fmask = 0,
tmask,
@@ -2022,6 +2029,9 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
* ereport'ing directly, but then there is no way
* to report the bad time zone name.
*/
+ if (!throw_errors)
+ return DTERR_BAD_FORMAT;
+
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("time zone \"%s\" not recognized",
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 49cdb290ac2..04a0e3eb063 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -139,18 +139,12 @@ anytimestamp_typmodout(bool istz, int32 typmod)
* USER I/O ROUTINES *
*****************************************************************************/
-/* timestamp_in()
- * Convert a string to internal form.
+/* timestamp_in_opt_error()
+ * Convert a string to internal form, returning error instead of throwing if needed.
*/
-Datum
-timestamp_in(PG_FUNCTION_ARGS)
+Timestamp
+timestamp_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
Timestamp result;
fsec_t fsec;
struct pg_tm tt,
@@ -166,17 +160,34 @@ timestamp_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "timestamp");
+ }
switch (dtype)
{
case DTK_DATE:
if (tm2timestamp(tm, fsec, NULL, &result) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range: \"%s\"", str)));
+ }
break;
case DTK_EPOCH:
@@ -197,11 +208,27 @@ timestamp_in(PG_FUNCTION_ARGS)
TIMESTAMP_NOEND(result);
}
- AdjustTimestampForTypmod(&result, typmod);
+ AdjustTimestampForTypmodError(&result, typmod, error);
PG_RETURN_TIMESTAMP(result);
}
+/* timestamp_in()
+ * Convert a string to internal form.
+ */
+Datum
+timestamp_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMESTAMP(timestamp_in_opt_error(str, typmod, NULL));
+}
+
/* timestamp_out()
* Convert a timestamp to external form.
*/
@@ -400,15 +427,9 @@ AdjustTimestampForTypmod(Timestamp *time, int32 typmod)
/* timestamptz_in()
* Convert a string to internal form.
*/
-Datum
-timestamptz_in(PG_FUNCTION_ARGS)
+TimestampTz
+timestamptz_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
TimestampTz result;
fsec_t fsec;
struct pg_tm tt,
@@ -424,17 +445,34 @@ timestamptz_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "timestamp with time zone");
+ }
switch (dtype)
{
case DTK_DATE:
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range: \"%s\"", str)));
+ }
break;
case DTK_EPOCH:
@@ -455,9 +493,24 @@ timestamptz_in(PG_FUNCTION_ARGS)
TIMESTAMP_NOEND(result);
}
- AdjustTimestampForTypmod(&result, typmod);
+ AdjustTimestampForTypmodError(&result, typmod, error);
- PG_RETURN_TIMESTAMPTZ(result);
+ return result;
+}
+
+/* timestamptz_in()
+ * Convert a string to internal form.
+ */
+Datum
+timestamptz_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMESTAMPTZ(timestamptz_in_opt_error(str, typmod, NULL));
}
/*
@@ -5614,8 +5667,8 @@ timestamptz_timestamp(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMP(timestamptz2timestamp(timestamp));
}
-static Timestamp
-timestamptz2timestamp(TimestampTz timestamp)
+Timestamp
+timestamptz2timestamp_opt_error(TimestampTz timestamp, bool *error)
{
Timestamp result;
struct pg_tm tt,
@@ -5628,17 +5681,40 @@ timestamptz2timestamp(TimestampTz timestamp)
else
{
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
+
if (tm2timestamp(tm, fsec, NULL, &result) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
}
return result;
}
+static Timestamp
+timestamptz2timestamp(TimestampTz timestamp)
+{
+ return timestamptz2timestamp_opt_error(timestamp, NULL);
+}
+
/* timestamptz_zone()
* Evaluate timestamp with time zone type at the specified time zone.
* Returns a timestamp without time zone.
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 9fbbfb1be54..7877bce6043 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -12866,7 +12866,7 @@ check_recovery_target_time(char **newval, void **extra, GucSource source)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz, true);
if (dterr != 0)
return false;
if (dtype != DTK_DATE)
diff --git a/src/include/utils/date.h b/src/include/utils/date.h
index 91ae24254df..f29168a025b 100644
--- a/src/include/utils/date.h
+++ b/src/include/utils/date.h
@@ -87,4 +87,17 @@ extern bool time_overflows(int hour, int min, int sec, fsec_t fsec);
extern bool float_time_overflows(int hour, int min, double sec);
extern void AdjustTimeForTypmod(TimeADT *time, int32 typmod);
+extern DateADT timestamp_date_opt_error(Timestamp timestamp, bool *error);
+extern TimeADT timestamp_time_opt_error(Timestamp timestamp,
+ bool *isnull, bool *error);
+
+extern DateADT timestamptz_date_opt_error(Timestamp timestamp, bool *error);
+extern TimeADT timestamptz_time_opt_error(TimestampTz timestamp,
+ bool *isnull, bool *error);
+extern TimeTzADT *timestamptz_timetz_opt_error(TimestampTz timestamp,
+ bool *error);
+extern DateADT date_in_opt_error(char *str, bool *error);
+extern TimeADT time_in_opt_error(char *str, int32 typmod, bool *error);
+extern TimeTzADT *timetz_in_opt_error(char *str, int32 typmod, bool *error);
+
#endif /* DATE_H */
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index 4527e825177..68241d7c4cc 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -295,11 +295,13 @@ extern int ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
int maxfields, int *numfields);
extern int DecodeDateTime(char **field, int *ftype,
int nf, int *dtype,
- struct pg_tm *tm, fsec_t *fsec, int *tzp);
+ struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors);
extern int DecodeTimezone(char *str, int *tzp);
extern int DecodeTimeOnly(char **field, int *ftype,
int nf, int *dtype,
- struct pg_tm *tm, fsec_t *fsec, int *tzp);
+ struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors);
extern int DecodeInterval(char **field, int *ftype, int nf, int range,
int *dtype, struct pg_itm_in *itm_in);
extern int DecodeISO8601Interval(char *str,
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index edf3a973186..2685d14342d 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -102,8 +102,14 @@ extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
int *overflow);
+extern Timestamp timestamptz2timestamp_opt_error(TimestampTz timestamp,
+ bool *error);
extern int32 timestamp_cmp_timestamptz_internal(Timestamp timestampVal,
TimestampTz dt2);
+extern Timestamp timestamp_in_opt_error(char *str, int32 typmod,
+ bool *error);
+extern TimestampTz timestamptz_in_opt_error(char *str, int32 typmod,
+ bool *error);
extern int isoweek2j(int year, int week);
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
--
2.17.1
v9-0006-Add-safe-input-function-for-jsonb.patch (text/x-patch, 3.1K) download
From 62a27e0b243cebc5e3eb8628cbb222533a02338f Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Mon, 29 Aug 2022 23:56:45 +0300
Subject: [PATCH v9 6/9] Add safe input function for jsonb
---
src/backend/utils/adt/jsonb.c | 26 ++++++++++++++++++--------
src/include/utils/jsonb.h | 2 ++
2 files changed, 20 insertions(+), 8 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index f700c5b4c93..1da519daaef 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -46,7 +46,6 @@ typedef struct JsonbAggState
Oid val_output_func;
} JsonbAggState;
-static inline Datum jsonb_from_cstring(char *json, int len, bool unique_keys);
static size_t checkStringLen(size_t len);
static void jsonb_in_object_start(void *pstate);
static void jsonb_in_object_end(void *pstate);
@@ -77,7 +76,7 @@ jsonb_in(PG_FUNCTION_ARGS)
{
char *json = PG_GETARG_CSTRING(0);
- return jsonb_from_cstring(json, strlen(json), false);
+ PG_RETURN_DATUM(jsonb_from_cstring(json, strlen(json), false, NULL));
}
/*
@@ -101,7 +100,7 @@ jsonb_recv(PG_FUNCTION_ARGS)
else
elog(ERROR, "unsupported jsonb version number %d", version);
- return jsonb_from_cstring(str, nbytes, false);
+ return jsonb_from_cstring(str, nbytes, false, NULL);
}
/*
@@ -147,7 +146,7 @@ jsonb_from_text(text *js, bool unique_keys)
{
return jsonb_from_cstring(VARDATA_ANY(js),
VARSIZE_ANY_EXHDR(js),
- unique_keys);
+ unique_keys, NULL);
}
/*
@@ -239,12 +238,13 @@ jsonb_typeof(PG_FUNCTION_ARGS)
*
* Uses the json parser (with hooks) to construct a jsonb.
*/
-static inline Datum
-jsonb_from_cstring(char *json, int len, bool unique_keys)
+Datum
+jsonb_from_cstring(char *json, int len, bool unique_keys, bool *error)
{
JsonLexContext *lex;
JsonbInState state;
JsonSemAction sem;
+ JsonParseErrorType result;
memset(&state, 0, sizeof(state));
memset(&sem, 0, sizeof(sem));
@@ -261,10 +261,20 @@ jsonb_from_cstring(char *json, int len, bool unique_keys)
sem.scalar = jsonb_in_scalar;
sem.object_field_start = jsonb_in_object_field_start;
- pg_parse_json_or_ereport(lex, &sem);
+ result = pg_parse_json(lex, &sem);
+ if (result != JSON_SUCCESS)
+ {
+ if (error)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+
+ json_ereport_error(result, lex);
+ }
/* after parsing, the item member has the composed jsonb structure */
- PG_RETURN_POINTER(JsonbValueToJsonb(state.res));
+ return JsonbPGetDatum(JsonbValueToJsonb(state.res));
}
static size_t
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index bae466b5234..d5574faa79e 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -420,6 +420,8 @@ extern void JsonbHashScalarValueExtended(const JsonbValue *scalarVal,
/* jsonb.c support functions */
extern Datum jsonb_from_text(text *js, bool unique_keys);
+extern Datum jsonb_from_cstring(char *json, int len, bool unique_keys,
+ bool *error);
extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
int estimated_len);
extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
--
2.17.1
v9-0007-Remove-subtransaction-in-SQL-JSON-execution.patch (text/x-patch, 106.5K) download
From 9154ff35f22fe7e8ce65ffdad757ff94abd79440 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Tue, 30 Aug 2022 00:10:44 +0300
Subject: [PATCH v9 7/9] Remove subtransaction in SQL/JSON execution
---
src/backend/executor/execExpr.c | 180 ++--
src/backend/executor/execExprInterp.c | 946 ++++++++++++--------
src/backend/jit/llvm/llvmjit_expr.c | 2 +-
src/backend/jit/llvm/llvmjit_types.c | 2 +-
src/backend/nodes/nodeFuncs.c | 91 +-
src/backend/optimizer/util/clauses.c | 73 +-
src/backend/parser/parse_expr.c | 226 ++---
src/backend/utils/adt/jsonpath.c | 8 +-
src/include/executor/execExpr.h | 49 +-
src/include/nodes/primnodes.h | 36 +-
src/include/optimizer/clauses.h | 2 +
src/include/utils/jsonpath.h | 3 +-
src/test/regress/expected/jsonb_sqljson.out | 327 +++++--
src/test/regress/sql/jsonb_sqljson.sql | 72 +-
14 files changed, 1131 insertions(+), 886 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index d0a57c7aaee..f50e0d703f8 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -86,7 +86,9 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
FunctionCallInfo fcinfo, AggStatePerTrans pertrans,
int transno, int setno, int setoff, bool ishash,
bool nullcheck);
-
+static void ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state,
+ ExprEvalStep *scratch,
+ Datum *resv, bool *resnull);
static ExprState *
ExecInitExprInternal(Expr *node, PlanState *parent, ParamListInfo ext_params,
@@ -2559,114 +2561,9 @@ ExecInitExprRec(Expr *node, ExprState *state,
}
case T_JsonExpr:
- {
- JsonExpr *jexpr = castNode(JsonExpr, node);
- JsonExprState *jsestate = palloc0(sizeof(JsonExprState));
- ListCell *argexprlc;
- ListCell *argnamelc;
-
- scratch.opcode = EEOP_JSONEXPR;
- scratch.d.jsonexpr.jsestate = jsestate;
-
- jsestate->jsexpr = jexpr;
-
- jsestate->formatted_expr =
- palloc(sizeof(*jsestate->formatted_expr));
-
- ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
- &jsestate->formatted_expr->value,
- &jsestate->formatted_expr->isnull);
-
- jsestate->pathspec =
- palloc(sizeof(*jsestate->pathspec));
-
- ExecInitExprRec((Expr *) jexpr->path_spec, state,
- &jsestate->pathspec->value,
- &jsestate->pathspec->isnull);
-
- jsestate->res_expr =
- palloc(sizeof(*jsestate->res_expr));
-
- jsestate->result_expr = jexpr->result_coercion
- ? ExecInitExprWithCaseValue((Expr *) jexpr->result_coercion->expr,
- state->parent,
- &jsestate->res_expr->value,
- &jsestate->res_expr->isnull)
- : NULL;
-
- jsestate->default_on_empty = !jexpr->on_empty ? NULL :
- ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
- state->parent);
-
- jsestate->default_on_error =
- ExecInitExpr((Expr *) jexpr->on_error->default_expr,
- state->parent);
-
- if (jexpr->omit_quotes ||
- (jexpr->result_coercion && jexpr->result_coercion->via_io))
- {
- Oid typinput;
-
- /* lookup the result type's input function */
- getTypeInputInfo(jexpr->returning->typid, &typinput,
- &jsestate->input.typioparam);
- fmgr_info(typinput, &jsestate->input.func);
- }
-
- jsestate->args = NIL;
-
- forboth(argexprlc, jexpr->passing_values,
- argnamelc, jexpr->passing_names)
- {
- Expr *argexpr = (Expr *) lfirst(argexprlc);
- String *argname = lfirst_node(String, argnamelc);
- JsonPathVariableEvalContext *var = palloc(sizeof(*var));
-
- var->name = pstrdup(argname->sval);
- var->typid = exprType((Node *) argexpr);
- var->typmod = exprTypmod((Node *) argexpr);
- var->estate = ExecInitExpr(argexpr, state->parent);
- var->econtext = NULL;
- var->mcxt = NULL;
- var->evaluated = false;
- var->value = (Datum) 0;
- var->isnull = true;
-
- jsestate->args =
- lappend(jsestate->args, var);
- }
-
- jsestate->cache = NULL;
-
- if (jexpr->coercions)
- {
- JsonCoercion **coercion;
- struct JsonCoercionState *cstate;
- Datum *caseval;
- bool *casenull;
-
- jsestate->coercion_expr =
- palloc(sizeof(*jsestate->coercion_expr));
-
- caseval = &jsestate->coercion_expr->value;
- casenull = &jsestate->coercion_expr->isnull;
-
- for (cstate = &jsestate->coercions.null,
- coercion = &jexpr->coercions->null;
- coercion <= &jexpr->coercions->composite;
- coercion++, cstate++)
- {
- cstate->coercion = *coercion;
- cstate->estate = *coercion ?
- ExecInitExprWithCaseValue((Expr *) (*coercion)->expr,
- state->parent,
- caseval, casenull) : NULL;
- }
- }
-
- ExprEvalPushStep(state, &scratch);
- break;
- }
+ ExecInitJsonExpr(castNode(JsonExpr, node), state, &scratch,
+ resv, resnull);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
@@ -4257,3 +4154,68 @@ ExecBuildParamSetEqual(TupleDesc desc,
return state;
}
+
+static void
+ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
+ Datum *resv, bool *resnull)
+{
+ JsonExprState *jsestate;
+ ListCell *argexprlc;
+ ListCell *argnamelc;
+
+ /* JSON_TABLE preudo-function returns context item as a result */
+ if (jexpr->op == JSON_TABLE_OP)
+ {
+ ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
+ resv, resnull);
+ return;
+ }
+
+ jsestate = palloc0(sizeof(JsonExprState));
+ jsestate->jsexpr = jexpr;
+
+ scratch->opcode = EEOP_JSONEXPR;
+ scratch->d.jsonexpr.jsestate = jsestate;
+
+ ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
+ &jsestate->formatted_expr.value,
+ &jsestate->formatted_expr.isnull);
+
+ ExecInitExprRec((Expr *) jexpr->path_spec, state,
+ &jsestate->pathspec.value,
+ &jsestate->pathspec.isnull);
+
+ if (jexpr->on_empty)
+ jsestate->default_on_empty =
+ ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
+ state->parent);
+
+ jsestate->default_on_error =
+ ExecInitExpr((Expr *) jexpr->on_error->default_expr,
+ state->parent);
+
+ jsestate->args = NIL;
+
+ forboth(argexprlc, jexpr->passing_values,
+ argnamelc, jexpr->passing_names)
+ {
+ Expr *argexpr = (Expr *) lfirst(argexprlc);
+ String *argname = lfirst_node(String, argnamelc);
+ JsonPathVariableEvalContext *var = palloc(sizeof(*var));
+
+ var->name = pstrdup(argname->sval);
+ var->typid = exprType((Node *) argexpr);
+ var->typmod = exprTypmod((Node *) argexpr);
+ var->estate = ExecInitExpr(argexpr, state->parent);
+ var->econtext = NULL;
+ var->mcxt = NULL;
+ var->evaluated = false;
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ jsestate->args =
+ lappend(jsestate->args, var);
+ }
+
+ ExprEvalPushStep(state, scratch);
+}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 636794ca6f1..45c648817ba 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -75,6 +75,7 @@
#include "utils/date.h"
#include "utils/datum.h"
#include "utils/expandedrecord.h"
+#include "utils/float.h"
#include "utils/json.h"
#include "utils/jsonb.h"
#include "utils/jsonfuncs.h"
@@ -1844,7 +1845,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_CASE(EEOP_JSONEXPR)
{
/* too complex for an inline implementation */
- ExecEvalJson(state, op, econtext);
+ ExecEvalJsonExpr(state, op, econtext);
EEO_NEXT();
}
@@ -4700,96 +4701,6 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
*op->resnull = isnull;
}
-/*
- * Evaluate a JSON error/empty behavior result.
- */
-static Datum
-ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
- ExprState *default_estate, bool *is_null)
-{
- *is_null = false;
-
- switch (behavior->btype)
- {
- case JSON_BEHAVIOR_EMPTY_ARRAY:
- return JsonbPGetDatum(JsonbMakeEmptyArray());
-
- case JSON_BEHAVIOR_EMPTY_OBJECT:
- return JsonbPGetDatum(JsonbMakeEmptyObject());
-
- case JSON_BEHAVIOR_TRUE:
- return BoolGetDatum(true);
-
- case JSON_BEHAVIOR_FALSE:
- return BoolGetDatum(false);
-
- case JSON_BEHAVIOR_NULL:
- case JSON_BEHAVIOR_UNKNOWN:
- case JSON_BEHAVIOR_EMPTY:
- *is_null = true;
- return (Datum) 0;
-
- case JSON_BEHAVIOR_DEFAULT:
- return ExecEvalExpr(default_estate, econtext, is_null);
-
- default:
- elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
- return (Datum) 0;
- }
-}
-
-/*
- * Evaluate a coercion of a JSON item to the target type.
- */
-static Datum
-ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext,
- Datum res, bool *isNull, void *p, bool *error)
-{
- ExprState *estate = p;
- JsonExprState *jsestate;
-
- if (estate) /* coerce using specified expression */
- return ExecEvalExpr(estate, econtext, isNull);
-
- jsestate = op->d.jsonexpr.jsestate;
-
- if (jsestate->jsexpr->op != JSON_EXISTS_OP)
- {
- JsonCoercion *coercion = jsestate->jsexpr->result_coercion;
- JsonExpr *jexpr = jsestate->jsexpr;
- Jsonb *jb = *isNull ? NULL : DatumGetJsonbP(res);
-
- if ((coercion && coercion->via_io) ||
- (jexpr->omit_quotes && !*isNull &&
- JB_ROOT_IS_SCALAR(jb)))
- {
- /* strip quotes and call typinput function */
- char *str = *isNull ? NULL : JsonbUnquote(jb);
-
- return InputFunctionCall(&jsestate->input.func, str,
- jsestate->input.typioparam,
- jexpr->returning->typmod);
- }
- else if (coercion && coercion->via_populate)
- return json_populate_type(res, JSONBOID,
- jexpr->returning->typid,
- jexpr->returning->typmod,
- &jsestate->cache,
- econtext->ecxt_per_query_memory,
- isNull);
- }
-
- if (jsestate->result_expr)
- {
- jsestate->res_expr->value = res;
- jsestate->res_expr->isnull = *isNull;
-
- res = ExecEvalExpr(jsestate->result_expr, econtext, isNull);
- }
-
- return res;
-}
-
/*
* Evaluate a JSON path variable caching computed value.
*/
@@ -4844,84 +4755,438 @@ EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
}
/*
- * Prepare SQL/JSON item coercion to the output type. Returned a datum of the
- * corresponding SQL type and a pointer to the coercion state.
+ * Check whether we need to override default coercion in
+ * JSON_QUERY(OMIT QUOTES) case.
*/
-Datum
-ExecPrepareJsonItemCoercion(JsonbValue *item,
- JsonReturning *returning,
- struct JsonCoercionsState *coercions,
- struct JsonCoercionState **pcoercion)
+static bool
+ExecJsonQueryNeedsIOCoercion(JsonExpr *jsexpr, Datum res, bool isnull)
{
- struct JsonCoercionState *coercion;
- Datum res;
- JsonbValue buf;
+ if (jsexpr->omit_quotes && !isnull)
+ {
+ Jsonb *jb = DatumGetJsonbP(res);
+ JsonbValue jbv;
+
+ /* Coerce string items via I/O in OMIT QUOTES case */
+ return JsonbExtractScalar(&jb->root, &jbv) &&
+ jbv.type == jbvString;
+ }
+
+ return false;
+}
- if (item->type == jbvBinary &&
- JsonContainerIsScalar(item->val.binary.data))
+/* Coerce C string to text, varchar(N), or bpchar(N) */
+static Datum
+ExecJsonStringCoercion(const char *str, int32 len, Oid typid, int32 typmod)
+{
+ if (typid == BYTEAOID)
{
- bool res PG_USED_FOR_ASSERTS_ONLY;
+ text *txt = cstring_to_text_with_len(str, len);
+ NameData encoding = {0};
+
+ /* UTF8 is the only one supported encoding */
+ strcpy(NameStr(encoding), "UTF8");
- res = JsonbExtractScalar(item->val.binary.data, &buf);
- item = &buf;
- Assert(res);
+ return DirectFunctionCall2(pg_convert_to,
+ PointerGetDatum(txt),
+ PointerGetDatum(&encoding));
}
+ else if (typid == TEXTOID || typid == JSONOID || typmod <= VARHDRSZ)
+ return PointerGetDatum(cstring_to_text_with_len(str, len));
+ else
+ {
+ char *txt;
+ int32 size;
+
+ len = VARHDRSZ + len;
+
+ if (typid == BPCHAROID)
+ size = typmod;
+ else
+ size = Min(len, typmod);
+
+ txt = palloc(size);
+ SET_VARSIZE(txt, size);
+
+ memcpy(VARDATA(txt), str, Min(size, len) - VARHDRSZ);
+
+ if (len < size)
+ memset(txt + len, ' ', size - len);
- /* get coercion state reference and datum of the corresponding SQL type */
+ return PointerGetDatum(txt);
+ }
+}
+
+/* Coerce SQL/JSON item to text */
+static Datum
+ExecJsonCoercionToText(PGFunction outfunc, Datum value, Oid typid, int32 typmod)
+{
+ char *str = DatumGetCString(DirectFunctionCall1(outfunc, value));
+
+ return ExecJsonStringCoercion(str, strlen(str), typid, typmod);
+}
+
+/* Coerce datetime SQL/JSON item to the output typid */
+static Datum
+ExecJsonDatetimeCoercion(Datum val, Oid val_typid, Oid typid, int32 typmod,
+ bool *isnull, bool *error)
+{
+ if (val_typid == typid)
+ return val;
+
+ /*
+ * XXX coercion to text is done using output functions, and they
+ * are mutable for non-time[tz] types due to using of DateStyle.
+ * We can pass USE_ISO_DATES, which is used inside jsonpath, to
+ * make these coercions and JSON_VALUE(RETURNING text) immutable.
+ *
+ * XXX Also timestamp[tz] output functions can throw "out of range"
+ * error, but this error seem to be not possible.
+ */
+ switch (val_typid)
+ {
+ case DATEOID:
+ if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ return ExecJsonCoercionToText(date_out, val, typid, typmod);
+ else if (typid == DATEOID)
+ return val;
+ else if (typid == TIMESTAMPOID)
+ {
+ int overflow = 0;
+ Timestamp ts =
+ date2timestamp_opt_overflow(DatumGetDateADT(val),
+ error ? &overflow : NULL);
+
+ if (overflow)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+
+ return TimestampGetDatum(ts);
+ }
+ else if (typid == TIMESTAMPTZOID)
+ {
+ int overflow = 0;
+ TimestampTz res =
+ date2timestamptz_opt_overflow(DatumGetDateADT(val),
+ error ? &overflow : NULL);
+
+ if (overflow)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+
+ return TimestampTzGetDatum(res);
+ }
+ else
+ break; /* No cast */
+
+ case TIMEOID:
+ if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ return ExecJsonCoercionToText(time_out, val, typid, typmod);
+ else if (typid == TIMEOID)
+ return val;
+ else if (typid == TIMETZOID)
+ return DirectFunctionCall1(time_timetz, val);
+ else
+ break; /* No cast */
+
+ case TIMETZOID:
+ if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ return ExecJsonCoercionToText(timetz_out, val, typid, typmod);
+ else if (typid == TIMETZOID)
+ return val;
+ else if (typid == TIMEOID)
+ return DirectFunctionCall1(timetz_time, val);
+ else
+ break;
+
+ case TIMESTAMPOID:
+ if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ return ExecJsonCoercionToText(timestamp_out, val, typid, typmod);
+ else if (typid == TIMESTAMPOID)
+ return val;
+ else if (typid == DATEOID)
+ return timestamp_date_opt_error(DatumGetTimestamp(val), error);
+ else if (typid == TIMEOID)
+ {
+ TimeADT time =
+ timestamp_time_opt_error(DatumGetTimestamp(val),
+ isnull, error);
+
+ if ((error && *error) || *isnull)
+ return (Datum) 0;
+ else
+ return TimeADTGetDatum(time);
+ }
+ else if (typid == TIMESTAMPTZOID)
+ {
+ int overflow = 0;
+ TimestampTz tstz =
+ timestamp2timestamptz_opt_overflow(DatumGetTimestamp(val),
+ error ? &overflow : NULL);
+
+ if (overflow)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+
+ return TimestampTzGetDatum(tstz);
+ }
+ else
+ break; /* No cast */
+
+ case TIMESTAMPTZOID:
+ if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ return ExecJsonCoercionToText(timestamptz_out, val, typid, typmod);
+ else if (typid == TIMESTAMPTZOID)
+ return val;
+ else if (typid == DATEOID)
+ return timestamptz_date_opt_error(DatumGetTimestampTz(val), error);
+ else if (typid == TIMEOID)
+ {
+ TimeADT time =
+ timestamptz_time_opt_error(DatumGetTimestampTz(val),
+ isnull, error);
+
+ if ((error && *error) || *isnull)
+ return (Datum) 0;
+ else
+ return TimeADTGetDatum(time);
+ }
+ else if (typid == TIMETZOID)
+ {
+ TimeTzADT *result =
+ timestamptz_timetz_opt_error(DatumGetTimestampTz(val), error);
+
+ if ((error && *error) || !result)
+ {
+ *isnull = true;
+ return (Datum) 0;
+ }
+
+ return TimeTzADTPGetDatum(result);
+ }
+ else if (typid == TIMESTAMPOID)
+ {
+ Timestamp ts =
+ timestamptz2timestamp_opt_error(DatumGetTimestampTz(val),
+ error);
+
+ if (error && *error)
+ return (Datum) 0;
+
+ return TimestampGetDatum(ts);
+ }
+ else
+ break; /* No cast */
+
+ default:
+ elog(ERROR, "unexpected jsonb datetime type oid %u", val_typid);
+ break;
+ }
+
+ if (!error)
+ ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+ errmsg("SQL/JSON item cannot be cast to target type")));
+
+ *error = true;
+ *isnull = true;
+
+ return (Datum) 0;
+}
+
+/* Coerce boolean SQL/JSON item or JSON_EXISTS result to the output type */
+static bool
+ExecJsonBoolCoercion(bool val, Oid typid, int32 typmod, Datum *res)
+{
+ if (typid == BOOLOID)
+ *res = BoolGetDatum(val);
+ else if (typid == INT4OID)
+ /* We only have cast bool::int4 in the catalog. */
+ *res = Int32GetDatum(val ? 1 : 0);
+ else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ /*
+ * bool::text returns 'true' / 'false',
+ * boolout() returns 't' / 'f'.
+ */
+ *res = ExecJsonStringCoercion(val ? "true" : "false", val ? 4 : 5,
+ typid, typmod);
+ else
+ return false;
+
+ return true;
+}
+
+static Datum
+JsonbPGetTextDatum(Jsonb *jb)
+{
+ char *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+ Datum res = CStringGetTextDatum(str);
+
+ pfree(str);
+ return res;
+}
+
+/* Coerce SQL/JSON item to the output typid */
+static Datum
+ExecJsonValueCoercion(JsonbValue *item, Oid typid, int32 typmod,
+ bool *isnull, bool *error)
+{
+ *isnull = false;
+
+ /* Special case for json and jsonb output types */
+ if (typid == JSONBOID)
+ return JsonbPGetDatum(JsonbValueToJsonb(item));
+
+ if (typid == JSONOID)
+ return JsonbPGetTextDatum(JsonbValueToJsonb(item));
+
+ /*
+ * Coercion method and set of supported output types are determined
+ * by the item type.
+ */
switch (item->type)
{
case jbvNull:
- coercion = &coercions->null;
- res = (Datum) 0;
- break;
+ Assert(0); /* must be handled by the caller */
+ *isnull = true;
+ return (Datum) 0;
case jbvString:
- coercion = &coercions->string;
- res = PointerGetDatum(cstring_to_text_with_len(item->val.string.val,
- item->val.string.len));
- break;
+ if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ {
+ return ExecJsonStringCoercion(item->val.string.val,
+ item->val.string.len,
+ typid, typmod);
+ }
+ else if (typid == INT2OID || typid == INT4OID || typid == INT8OID)
+ {
+ char *str = pnstrdup(item->val.string.val,
+ item->val.string.len);
+
+ if (error)
+ {
+ int64 val = pg_strtoint64_opt_error(str, error);
+
+ if (*error)
+ return (Datum) 0;
+
+ if (typid == INT2OID)
+ {
+ if (val <= PG_INT16_MAX || val >= PG_INT16_MIN)
+ return Int16GetDatum((int16) val);
+ }
+ else if (typid == INT4OID)
+ {
+ if (val <= PG_INT32_MAX || val >= PG_INT32_MIN)
+ return Int32GetDatum((int32) val);
+ }
+ else
+ return Int64GetDatum(val);
+
+ *error = true;
+ return (Datum) 0;
+ }
+ else if (typid == INT2OID)
+ return Int16GetDatum(pg_strtoint16(str));
+ else if (typid == INT4OID)
+ return Int32GetDatum(pg_strtoint32(str));
+ else
+ return Int64GetDatum(pg_strtoint64(str));
+ }
+ else if (typid == BOOLOID)
+ {
+ return BoolGetDatum(boolin_opt_error(item->val.string.val,
+ item->val.string.len,
+ error));
+ }
+ else
+ {
+ char *str = pnstrdup(item->val.string.val,
+ item->val.string.len);
+
+ if (typid == FLOAT4OID)
+ return Float4GetDatum(float4in_opt_error(str, error));
+ else if (typid == FLOAT8OID)
+ return Float8GetDatum(
+ float8in_internal_opt_error(str, NULL,
+ "double precision",
+ str, error));
+ else if (typid == NUMERICOID)
+ return NumericGetDatum(numeric_in_opt_error(str, typmod, error));
+ else if (typid == DATEOID)
+ return DateADTGetDatum(date_in_opt_error(str, error));
+ else if (typid == TIMEOID)
+ return TimeADTGetDatum(time_in_opt_error(str, typmod, error));
+ else if (typid == TIMETZOID)
+ return TimeTzADTPGetDatum(timetz_in_opt_error(str, typmod, error));
+ else if (typid == TIMESTAMPOID)
+ return TimestampGetDatum(timestamp_in_opt_error(str, typmod, error));
+ else if (typid == TIMESTAMPTZOID)
+ return TimestampTzGetDatum(timestamptz_in_opt_error(str, typmod, error));
+ else
+ break; /* No cast */
+ }
case jbvNumeric:
- coercion = &coercions->numeric;
- res = NumericGetDatum(item->val.numeric);
- break;
+ {
+ Numeric num = item->val.numeric;
+
+ if (typid == NUMERICOID)
+ return NumericGetDatum(num);
+ else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+ return ExecJsonCoercionToText(numeric_out, NumericGetDatum(num), typid, typmod);
+ else if (typid == INT2OID)
+ return Int16GetDatum(numeric_int2_opt_error(num, error));
+ else if (typid == INT4OID)
+ return Int32GetDatum(numeric_int4_opt_error(num, error));
+ else if (typid == INT8OID)
+ return Int64GetDatum(numeric_int8_opt_error(num, error));
+ else if (typid == FLOAT4OID || typid == FLOAT8OID)
+ {
+ /*
+ * XXX numeric_float8() also uses I/O coercion, but
+ * it has special handling of Inf and NaN.
+ */
+ Datum cstr = DirectFunctionCall1(numeric_out,
+ NumericGetDatum(num));
+ char *str = DatumGetCString(cstr);
- case jbvBool:
- coercion = &coercions->boolean;
- res = BoolGetDatum(item->val.boolean);
- break;
+ if (typid == FLOAT4OID)
+ return Float4GetDatum(float4in_opt_error(str, error));
+ else
+ return Float8GetDatum(
+ float8in_internal_opt_error(str, NULL,
+ "double precision",
+ str, error));
+ }
+ else
+ break; /* No cast */
+ }
- case jbvDatetime:
- res = item->val.datetime.value;
- switch (item->val.datetime.typid)
+ case jbvBool:
{
- case DATEOID:
- coercion = &coercions->date;
- break;
- case TIMEOID:
- coercion = &coercions->time;
- break;
- case TIMETZOID:
- coercion = &coercions->timetz;
- break;
- case TIMESTAMPOID:
- coercion = &coercions->timestamp;
- break;
- case TIMESTAMPTZOID:
- coercion = &coercions->timestamptz;
- break;
- default:
- elog(ERROR, "unexpected jsonb datetime type oid %u",
- item->val.datetime.typid);
- return (Datum) 0;
+ Datum res;
+
+ if (ExecJsonBoolCoercion(item->val.boolean, typid, typmod, &res))
+ return res;
+
+ break; /* No cast */
}
- break;
+
+ case jbvDatetime:
+ return ExecJsonDatetimeCoercion(item->val.datetime.value,
+ item->val.datetime.typid,
+ typid, typmod, isnull, error);
case jbvArray:
case jbvObject:
case jbvBinary:
- coercion = &coercions->composite;
- res = JsonbPGetDatum(JsonbValueToJsonb(item));
+ Assert(0); /* non-scalars must be rejected by JsonPathValue() */
break;
default:
@@ -4929,99 +5194,100 @@ ExecPrepareJsonItemCoercion(JsonbValue *item,
return (Datum) 0;
}
- *pcoercion = coercion;
+ if (!error)
+ ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+ errmsg("SQL/JSON item cannot be cast to target type")));
- return res;
-}
+ *error = true;
+ *isnull = true;
-typedef Datum (*JsonFunc) (ExprEvalStep *op, ExprContext *econtext,
- Datum item, bool *resnull, void *p, bool *error);
+ return (Datum) 0;
+}
+/*
+ * Evaluate a JSON error/empty behavior and coerce result to the output
+ * type.
+ */
static Datum
-ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
- ExprContext *econtext,
- Datum res, bool *resnull,
- void *p, bool *error, bool subtrans)
+ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
+ ExprState *default_estate,
+ Oid ret_typid, int32 ret_typmod, bool *is_null)
{
- MemoryContext oldcontext;
- ResourceOwner oldowner;
-
- if (!subtrans)
- /* No need to use subtransactions. */
- return func(op, econtext, res, resnull, p, error);
+ *is_null = false;
- /*
- * We should catch exceptions of category ERRCODE_DATA_EXCEPTION and
- * execute the corresponding ON ERROR behavior then.
- */
- oldcontext = CurrentMemoryContext;
- oldowner = CurrentResourceOwner;
+ switch (behavior->btype)
+ {
+ case JSON_BEHAVIOR_EMPTY_ARRAY:
+ if (ret_typid == JSONBOID)
+ return JsonbPGetDatum(JsonbMakeEmptyArray());
+ else
+ {
+ Assert(ret_typid == JSONOID || ret_typid == TEXTOID ||
+ ret_typid == VARCHAROID || ret_typid == BPCHAROID ||
+ ret_typid == BYTEAOID);
- Assert(error);
+ return ExecJsonStringCoercion("[]", 2, ret_typid, ret_typmod);
+ }
- BeginInternalSubTransaction(NULL);
- /* Want to execute expressions inside function's memory context */
- MemoryContextSwitchTo(oldcontext);
+ case JSON_BEHAVIOR_EMPTY_OBJECT:
+ if (ret_typid == JSONBOID)
+ return JsonbPGetDatum(JsonbMakeEmptyObject());
+ else
+ {
+ Assert(ret_typid == JSONOID || ret_typid == TEXTOID ||
+ ret_typid == VARCHAROID || ret_typid == BPCHAROID ||
+ ret_typid == BYTEAOID);
- PG_TRY();
- {
- res = func(op, econtext, res, resnull, p, error);
+ return ExecJsonStringCoercion("{}", 2, ret_typid, ret_typmod);
+ }
- /* Commit the inner transaction, return to outer xact context */
- ReleaseCurrentSubTransaction();
- MemoryContextSwitchTo(oldcontext);
- CurrentResourceOwner = oldowner;
- }
- PG_CATCH();
- {
- ErrorData *edata;
- int ecategory;
+ case JSON_BEHAVIOR_TRUE:
+ case JSON_BEHAVIOR_FALSE:
+ {
+ Datum res;
+ bool ok =
+ ExecJsonBoolCoercion(behavior->btype == JSON_BEHAVIOR_TRUE,
+ ret_typid, ret_typmod, &res);
- /* Save error info in oldcontext */
- MemoryContextSwitchTo(oldcontext);
- edata = CopyErrorData();
- FlushErrorState();
+ Assert(ok); /* returning type must be checked in parser */
- /* Abort the inner transaction */
- RollbackAndReleaseCurrentSubTransaction();
- MemoryContextSwitchTo(oldcontext);
- CurrentResourceOwner = oldowner;
+ return res;
+ }
- ecategory = ERRCODE_TO_CATEGORY(edata->sqlerrcode);
+ case JSON_BEHAVIOR_NULL:
+ case JSON_BEHAVIOR_UNKNOWN:
+ case JSON_BEHAVIOR_EMPTY:
+ *is_null = true;
+ return (Datum) 0;
- if (ecategory != ERRCODE_DATA_EXCEPTION && /* jsonpath and other data
- * errors */
- ecategory != ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION) /* domain errors */
- ReThrowError(edata);
+ case JSON_BEHAVIOR_DEFAULT:
+ /*
+ * Execute DEFAULT expression.
+ * Coercion is not needed here, because expression is
+ * already coerced to the target type by the parser.
+ */
+ return ExecEvalExpr(default_estate, econtext, is_null);
- res = (Datum) 0;
- *error = true;
+ default:
+ elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
+ return (Datum) 0;
}
- PG_END_TRY();
-
- return res;
}
-
-typedef struct
-{
- JsonPath *path;
- bool *error;
- bool coercionInSubtrans;
-} ExecEvalJsonExprContext;
-
static Datum
-ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
- Datum item, bool *resnull, void *pcxt,
- bool *error)
+ExecEvalJsonExprInternal(ExprState *state,
+ JsonExprState *jsestate, ExprContext *econtext,
+ JsonPath *path, Datum item, bool *resnull,
+ bool *error)
{
- ExecEvalJsonExprContext *cxt = pcxt;
- JsonPath *path = cxt->path;
- JsonExprState *jsestate = op->d.jsonexpr.jsestate;
JsonExpr *jexpr = jsestate->jsexpr;
- ExprState *estate = NULL;
bool empty = false;
Datum res = (Datum) 0;
+ Oid ret_typid = jexpr->returning->typid;
+ int32 ret_typmod = jexpr->returning->typmod;
+
+ *resnull = true;
switch (jexpr->op)
{
@@ -5029,70 +5295,96 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error,
jsestate->args);
if (error && *error)
- {
- *resnull = true;
return (Datum) 0;
- }
+
+ if (empty)
+ break;
+
*resnull = !DatumGetPointer(res);
- break;
+
+ /* Override default coercion in OMIT QUOTES case */
+ if (ExecJsonQueryNeedsIOCoercion(jexpr, res, *resnull))
+ {
+ char *str = JsonbUnquote(DatumGetJsonbP(res));
+
+ if (ret_typid == JSONBOID)
+ {
+ res = jsonb_from_cstring(str, strlen(str), false, error);
+
+ if (error && *error)
+ return (Datum) 0;
+
+ return res;
+ }
+ else if (ret_typid == JSONOID)
+ {
+ text *json = cstring_to_text(str);
+ JsonLexContext *lex;
+ JsonParseErrorType result;
+
+ /* validate it */
+ lex = makeJsonLexContext(json, false);
+ result = pg_parse_json(lex, &nullSemAction);
+
+ if (result != JSON_SUCCESS)
+ {
+ if (error)
+ {
+ *error = true;
+ return (Datum) 0;
+ }
+
+ json_ereport_error(result, lex);
+ }
+
+ return PointerGetDatum(json);
+ }
+ else if (ret_typid == TEXTOID ||
+ ret_typid == VARCHAROID ||
+ ret_typid == BPCHAROID ||
+ ret_typid == BYTEAOID)
+ return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod);
+ }
+ else if (ret_typid == JSONBOID)
+ return res;
+ else if (ret_typid == JSONOID || ret_typid == TEXTOID)
+ return JsonbPGetTextDatum(DatumGetJsonbP(res));
+ else if (ret_typid == VARCHAROID || ret_typid == BPCHAROID ||
+ ret_typid == BYTEAOID)
+ {
+ Jsonb *jb = DatumGetJsonbP(res);
+ char *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+
+ return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod);
+ }
+
+ Assert(0); /* unsupported output type */
+ *error = *resnull = true;
+ return (Datum) 0;
case JSON_VALUE_OP:
{
- struct JsonCoercionState *jcstate;
JsonbValue *jbv = JsonPathValue(item, path, &empty, error,
jsestate->args);
if (error && *error)
return (Datum) 0;
- if (!jbv) /* NULL or empty */
+ if (empty)
break;
- Assert(!empty);
-
- *resnull = false;
+ if (!jbv)
+ return (Datum) 0; /* NULL */
- /* coerce scalar item to the output type */
- if (jexpr->returning->typid == JSONOID ||
- jexpr->returning->typid == JSONBOID)
- {
- /* Use result coercion from json[b] to the output type */
- res = JsonbPGetDatum(JsonbValueToJsonb(jbv));
- break;
- }
+ Assert(jbv->type != jbvNull);
- /* Use coercion from SQL/JSON item type to the output type */
- res = ExecPrepareJsonItemCoercion(jbv,
- jsestate->jsexpr->returning,
- &jsestate->coercions,
- &jcstate);
+ res = ExecJsonValueCoercion(jbv, ret_typid, ret_typmod,
+ resnull, error);
- if (jcstate->coercion &&
- (jcstate->coercion->via_io ||
- jcstate->coercion->via_populate))
- {
- if (error)
- {
- *error = true;
- return (Datum) 0;
- }
-
- /*
- * Coercion via I/O means here that the cast to the target
- * type simply does not exist.
- */
- ereport(ERROR,
- (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
- errmsg("SQL/JSON item cannot be cast to target type")));
- }
- else if (!jcstate->estate)
- return res; /* no coercion */
+ if (error && *error)
+ return (Datum) 0;
- /* coerce using specific expression */
- estate = jcstate->estate;
- jsestate->coercion_expr->value = res;
- jsestate->coercion_expr->isnull = *resnull;
- break;
+ return res;
}
case JSON_EXISTS_OP:
@@ -5101,113 +5393,68 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
jsestate->args,
error);
- *resnull = error && *error;
- res = BoolGetDatum(exists);
-
- if (!jsestate->result_expr)
- return res;
+ if (!error || !*error)
+ {
+ /* Should succeed, output type is checked by parser */
+ (void) ExecJsonBoolCoercion(exists, ret_typid, ret_typmod, &res);
+ *resnull = false;
+ }
- /* coerce using result expression */
- estate = jsestate->result_expr;
- jsestate->res_expr->value = res;
- jsestate->res_expr->isnull = *resnull;
- break;
+ return res;
}
- case JSON_TABLE_OP:
- *resnull = false;
- return item;
-
default:
elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op);
return (Datum) 0;
}
- if (empty)
- {
- Assert(jexpr->on_empty); /* it is not JSON_EXISTS */
+ Assert(empty);
+ Assert(jexpr->on_empty); /* it is not JSON_EXISTS */
- if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+ if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+ {
+ if (error)
{
- if (error)
- {
- *error = true;
- return (Datum) 0;
- }
-
- ereport(ERROR,
- (errcode(ERRCODE_NO_SQL_JSON_ITEM),
- errmsg("no SQL/JSON item")));
+ *error = true;
+ return (Datum) 0;
}
- if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT)
-
- /*
- * Execute DEFAULT expression as a coercion expression, because
- * its result is already coerced to the target type.
- */
- estate = jsestate->default_on_empty;
- else
- /* Execute ON EMPTY behavior */
- res = ExecEvalJsonBehavior(econtext, jexpr->on_empty,
- jsestate->default_on_empty,
- resnull);
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_SQL_JSON_ITEM),
+ errmsg("no SQL/JSON item")));
}
- return ExecEvalJsonExprSubtrans(ExecEvalJsonExprCoercion, op, econtext,
- res, resnull, estate, error,
- cxt->coercionInSubtrans);
-}
-
-bool
-ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
- struct JsonCoercionsState *coercions)
-{
- if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
- return false;
-
- if (jsexpr->op == JSON_EXISTS_OP && !jsexpr->result_coercion)
- return false;
-
- if (!coercions)
- return true;
-
- return false;
+ /* Execute ON EMPTY behavior */
+ return ExecEvalJsonBehavior(econtext, jexpr->on_empty,
+ jsestate->default_on_empty,
+ ret_typid, ret_typmod, resnull);
}
/* ----------------------------------------------------------------
- * ExecEvalJson
+ * ExecEvalJsonExpr
* ----------------------------------------------------------------
*/
void
-ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
{
- ExecEvalJsonExprContext cxt;
JsonExprState *jsestate = op->d.jsonexpr.jsestate;
JsonExpr *jexpr = jsestate->jsexpr;
Datum item;
- Datum res = (Datum) 0;
+ Datum res;
JsonPath *path;
ListCell *lc;
bool error = false;
- bool needSubtrans;
bool throwErrors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR;
- *op->resnull = true; /* until we get a result */
- *op->resvalue = (Datum) 0;
-
- if (jsestate->formatted_expr->isnull || jsestate->pathspec->isnull)
+ if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull)
{
- /* execute domain checks for NULLs */
- (void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull,
- NULL, NULL);
-
- Assert(*op->resnull);
+ *op->resnull = true;
+ *op->resvalue = (Datum) 0;
return;
}
- item = jsestate->formatted_expr->value;
- path = DatumGetJsonPathP(jsestate->pathspec->value);
+ item = jsestate->formatted_expr.value;
+ path = DatumGetJsonPathP(jsestate->pathspec.value);
/* reset JSON path variable contexts */
foreach(lc, jsestate->args)
@@ -5218,29 +5465,20 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
var->evaluated = false;
}
- needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &jsestate->coercions);
-
- cxt.path = path;
- cxt.error = throwErrors ? NULL : &error;
- cxt.coercionInSubtrans = !needSubtrans && !throwErrors;
- Assert(!needSubtrans || cxt.error);
-
- res = ExecEvalJsonExprSubtrans(ExecEvalJsonExpr, op, econtext, item,
- op->resnull, &cxt, cxt.error,
- needSubtrans);
+ res = ExecEvalJsonExprInternal(state, jsestate, econtext,
+ path, item, op->resnull,
+ throwErrors ? NULL : &error);
if (error)
{
+ Assert(!throwErrors);
+
/* Execute ON ERROR behavior */
res = ExecEvalJsonBehavior(econtext, jexpr->on_error,
jsestate->default_on_error,
+ jexpr->returning->typid,
+ jexpr->returning->typmod,
op->resnull);
-
- /* result is already coerced in DEFAULT behavior case */
- if (jexpr->on_error->btype != JSON_BEHAVIOR_DEFAULT)
- res = ExecEvalJsonExprCoercion(op, econtext, res,
- op->resnull,
- NULL, NULL);
}
*op->resvalue = res;
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index bd3965143da..fd72630f5e6 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2408,7 +2408,7 @@ llvm_compile_expr(ExprState *state)
break;
case EEOP_JSONEXPR:
- build_EvalXFunc(b, mod, "ExecEvalJson",
+ build_EvalXFunc(b, mod, "ExecEvalJsonExpr",
v_state, op, v_econtext);
LLVMBuildBr(b, opblocks[opno + 1]);
break;
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index 373471ad27f..37fe64654b6 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -135,7 +135,7 @@ void *referenced_functions[] =
ExecEvalXmlExpr,
ExecEvalJsonConstructor,
ExecEvalJsonIsPredicate,
- ExecEvalJson,
+ ExecEvalJsonExpr,
MakeExpandedObjectReadOnlyInternal,
slot_getmissingattrs,
slot_getsomeattrs_int,
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c334daae392..e0240beeeab 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -266,9 +266,6 @@ exprType(const Node *expr)
case T_JsonExpr:
type = ((const JsonExpr *) expr)->returning->typid;
break;
- case T_JsonCoercion:
- type = exprType(((const JsonCoercion *) expr)->expr);
- break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
type = InvalidOid; /* keep compiler quiet */
@@ -507,8 +504,6 @@ exprTypmod(const Node *expr)
return ((const JsonConstructorExpr *) expr)->returning->typmod;
case T_JsonExpr:
return ((JsonExpr *) expr)->returning->typmod;
- case T_JsonCoercion:
- return exprTypmod(((const JsonCoercion *) expr)->expr);
default:
break;
}
@@ -1010,14 +1005,12 @@ exprCollation(const Node *expr)
case T_JsonExpr:
{
JsonExpr *jexpr = (JsonExpr *) expr;
- JsonCoercion *coercion = jexpr->result_coercion;
- if (!coercion)
- coll = InvalidOid;
- else if (coercion->expr)
- coll = exprCollation(coercion->expr);
- else if (coercion->via_io || coercion->via_populate)
- coll = coercion->collation;
+ if (jexpr->returning->typid == TEXTOID ||
+ jexpr->returning->typid == CHAROID ||
+ jexpr->returning->typid == VARCHAROID ||
+ jexpr->returning->typid == BPCHAROID)
+ coll = jexpr->collation;
else
coll = InvalidOid;
}
@@ -1255,14 +1248,12 @@ exprSetCollation(Node *expr, Oid collation)
case T_JsonExpr:
{
JsonExpr *jexpr = (JsonExpr *) expr;
- JsonCoercion *coercion = jexpr->result_coercion;
- if (!coercion)
- Assert(!OidIsValid(collation));
- else if (coercion->expr)
- exprSetCollation(coercion->expr, collation);
- else if (coercion->via_io || coercion->via_populate)
- coercion->collation = collation;
+ if (jexpr->returning->typid == TEXTOID ||
+ jexpr->returning->typid == CHAROID ||
+ jexpr->returning->typid == VARCHAROID ||
+ jexpr->returning->typid == BPCHAROID)
+ jexpr->collation = collation;
else
Assert(!OidIsValid(collation));
}
@@ -2507,8 +2498,6 @@ expression_tree_walker(Node *node,
if (walker(jexpr->formatted_expr, context))
return true;
- if (walker(jexpr->result_coercion, context))
- return true;
if (walker(jexpr->passing_values, context))
return true;
/* we assume walker doesn't care about passing_names */
@@ -2517,36 +2506,6 @@ expression_tree_walker(Node *node,
return true;
if (walker(jexpr->on_error->default_expr, context))
return true;
- if (walker(jexpr->coercions, context))
- return true;
- }
- break;
- case T_JsonCoercion:
- return walker(((JsonCoercion *) node)->expr, context);
- case T_JsonItemCoercions:
- {
- JsonItemCoercions *coercions = (JsonItemCoercions *) node;
-
- if (walker(coercions->null, context))
- return true;
- if (walker(coercions->string, context))
- return true;
- if (walker(coercions->numeric, context))
- return true;
- if (walker(coercions->boolean, context))
- return true;
- if (walker(coercions->date, context))
- return true;
- if (walker(coercions->time, context))
- return true;
- if (walker(coercions->timetz, context))
- return true;
- if (walker(coercions->timestamp, context))
- return true;
- if (walker(coercions->timestamptz, context))
- return true;
- if (walker(coercions->composite, context))
- return true;
}
break;
default:
@@ -3576,7 +3535,6 @@ expression_tree_mutator(Node *node,
FLATCOPY(newnode, jexpr, JsonExpr);
MUTATE(newnode->path_spec, jexpr->path_spec, Node *);
MUTATE(newnode->formatted_expr, jexpr->formatted_expr, Node *);
- MUTATE(newnode->result_coercion, jexpr->result_coercion, JsonCoercion *);
MUTATE(newnode->passing_values, jexpr->passing_values, List *);
/* assume mutator does not care about passing_names */
if (newnode->on_empty)
@@ -3587,35 +3545,6 @@ expression_tree_mutator(Node *node,
return (Node *) newnode;
}
break;
- case T_JsonCoercion:
- {
- JsonCoercion *coercion = (JsonCoercion *) node;
- JsonCoercion *newnode;
-
- FLATCOPY(newnode, coercion, JsonCoercion);
- MUTATE(newnode->expr, coercion->expr, Node *);
- return (Node *) newnode;
- }
- break;
- case T_JsonItemCoercions:
- {
- JsonItemCoercions *coercions = (JsonItemCoercions *) node;
- JsonItemCoercions *newnode;
-
- FLATCOPY(newnode, coercions, JsonItemCoercions);
- MUTATE(newnode->null, coercions->null, JsonCoercion *);
- MUTATE(newnode->string, coercions->string, JsonCoercion *);
- MUTATE(newnode->numeric, coercions->numeric, JsonCoercion *);
- MUTATE(newnode->boolean, coercions->boolean, JsonCoercion *);
- MUTATE(newnode->date, coercions->date, JsonCoercion *);
- MUTATE(newnode->time, coercions->time, JsonCoercion *);
- MUTATE(newnode->timetz, coercions->timetz, JsonCoercion *);
- MUTATE(newnode->timestamp, coercions->timestamp, JsonCoercion *);
- MUTATE(newnode->timestamptz, coercions->timestamptz, JsonCoercion *);
- MUTATE(newnode->composite, coercions->composite, JsonCoercion *);
- return (Node *) newnode;
- }
- break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 533df86ff77..6eb923892f4 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -411,6 +411,26 @@ contain_mutable_functions_walker(Node *node, void *context)
{
JsonExpr *jexpr = castNode(JsonExpr, node);
Const *cnst;
+ bool returns_datetime;
+
+ /*
+ * Input fuctions for datetime types are stable. They can be
+ * called in JSON_VALUE(), when the resulting SQL/JSON is a
+ * string.
+ */
+ if (jexpr->returning->typid == DATEOID ||
+ jexpr->returning->typid == TIMEOID ||
+ jexpr->returning->typid == TIMETZOID ||
+ jexpr->returning->typid == TIMESTAMPOID ||
+ jexpr->returning->typid == TIMESTAMPTZOID)
+ return true;
+
+ /*
+ * pg_convert_to(), which is used for implementation of
+ * JSON_QUERY(RETURNING bytea FORMAT JSON), is stable.
+ */
+ if (jexpr->returning->typid == BYTEAOID)
+ return true;
if (!IsA(jexpr->path_spec, Const))
return true;
@@ -421,8 +441,32 @@ contain_mutable_functions_walker(Node *node, void *context)
if (cnst->constisnull)
return false;
- return jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
- jexpr->passing_names, jexpr->passing_values);
+ if (jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
+ jexpr->passing_names, jexpr->passing_values,
+ &returns_datetime))
+ return true;
+
+ if (returns_datetime && jexpr->op == JSON_VALUE_OP)
+ {
+ /*
+ * Some datetime types have mutable output functions,
+ * so if returning string types whole expression is mutable.
+ * TODO check individual datetime type if it is known.
+ *
+ * Other non-JSON output types have no conversion from
+ * datetime types.
+ *
+ * Datetime returning types were checked above, no need to
+ * check them here. But conversion between them also can
+ * be mutable.
+ */
+ if (jexpr->returning->typid == TEXTOID ||
+ jexpr->returning->typid == VARCHAROID ||
+ jexpr->returning->typid == BPCHAROID)
+ return true;
+ }
+
+ return false;
}
if (IsA(node, SQLValueFunction))
@@ -896,18 +940,6 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
context, 0);
}
- /* JsonExpr is parallel-unsafe if subtransactions can be used. */
- else if (IsA(node, JsonExpr))
- {
- JsonExpr *jsexpr = (JsonExpr *) node;
-
- if (ExecEvalJsonNeedsSubTransaction(jsexpr, NULL))
- {
- context->max_hazard = PROPARALLEL_UNSAFE;
- return true;
- }
- }
-
/* Recurse to check arguments */
return expression_tree_walker(node,
max_parallel_hazard_walker,
@@ -5323,3 +5355,16 @@ pull_paramids_walker(Node *node, Bitmapset **context)
return expression_tree_walker(node, pull_paramids_walker,
(void *) context);
}
+
+bool
+expr_can_throw_errors(Node *expr)
+{
+ if (!expr)
+ return false;
+
+ if (IsA(expr, Const))
+ return false;
+
+ /* TODO consider more cases */
+ return true;
+}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index fabb5f72076..c02530a3671 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -22,6 +22,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
#include "parser/parse_agg.h"
@@ -4108,7 +4109,6 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func)
/* format is determined by context item type */
format = exprType(jsexpr->formatted_expr) == JSONBOID ? JS_FORMAT_JSONB : JS_FORMAT_JSON;
- jsexpr->result_coercion = NULL;
jsexpr->omit_quotes = false;
jsexpr->format = func->common->expr->format;
@@ -4169,40 +4169,6 @@ assignDefaultJsonReturningType(Node *context_item, JsonFormat *context_format,
ret->typmod = -1;
}
-/*
- * Try to coerce expression to the output type or
- * use json_populate_type() for composite, array and domain types or
- * use coercion via I/O.
- */
-static JsonCoercion *
-coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning)
-{
- char typtype;
- JsonCoercion *coercion = makeNode(JsonCoercion);
-
- coercion->expr = coerceJsonFuncExpr(pstate, expr, returning, false);
-
- if (coercion->expr)
- {
- if (coercion->expr == expr)
- coercion->expr = NULL;
-
- return coercion;
- }
-
- typtype = get_typtype(returning->typid);
-
- if (returning->typid == RECORDOID ||
- typtype == TYPTYPE_COMPOSITE ||
- typtype == TYPTYPE_DOMAIN ||
- type_is_array(returning->typid))
- coercion->via_populate = true;
- else
- coercion->via_io = true;
-
- return coercion;
-}
-
/*
* Transform a JSON output clause of JSON_VALUE and JSON_QUERY.
*/
@@ -4210,8 +4176,6 @@ static void
transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
JsonExpr *jsexpr)
{
- Node *expr = jsexpr->formatted_expr;
-
jsexpr->returning = transformJsonOutput(pstate, func->output, false);
/* JSON_VALUE returns text by default */
@@ -4225,14 +4189,41 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
{
JsonReturning ret;
- if (func->op == JSON_VALUE_OP &&
- jsexpr->returning->typid != JSONOID &&
- jsexpr->returning->typid != JSONBOID)
+ if (func->op == JSON_VALUE_OP)
{
- /* Forced coercion via I/O for JSON_VALUE for non-JSON types */
- jsexpr->result_coercion = makeNode(JsonCoercion);
- jsexpr->result_coercion->expr = NULL;
- jsexpr->result_coercion->via_io = true;
+ /*
+ * Only a limited list of output types is supported in
+ * JSON_VALUE() now.
+ */
+ switch (jsexpr->returning->typid)
+ {
+ case JSONBOID:
+ case JSONOID:
+ case TEXTOID:
+ case VARCHAROID:
+ case BPCHAROID:
+ case BOOLOID:
+ case NUMERICOID:
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case DATEOID:
+ case TIMEOID:
+ case TIMETZOID:
+ case TIMESTAMPOID:
+ case TIMESTAMPTZOID:
+ break; /* Ok */
+
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("returning type %s is not supported in JSON_VALUE()",
+ format_type_be(jsexpr->returning->typid)),
+ parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+ }
+
return;
}
@@ -4241,13 +4232,23 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
if (ret.typid != jsexpr->returning->typid ||
ret.typmod != jsexpr->returning->typmod)
{
- Node *placeholder = makeCaseTestExpr(expr);
-
- Assert(((CaseTestExpr *) placeholder)->typeId == ret.typid);
- Assert(((CaseTestExpr *) placeholder)->typeMod == ret.typmod);
+ switch (jsexpr->returning->typid)
+ {
+ case JSONBOID:
+ case JSONOID:
+ case TEXTOID:
+ case VARCHAROID:
+ case BPCHAROID:
+ case BYTEAOID:
+ break;
- jsexpr->result_coercion = coerceJsonExpr(pstate, placeholder,
- jsexpr->returning);
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("returning type %s is not supported in JSON_QUERY()",
+ format_type_be(jsexpr->returning->typid)),
+ parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+ }
}
}
else
@@ -4259,8 +4260,10 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
* Coerce an expression in JSON DEFAULT behavior to the target output type.
*/
static Node *
-coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
+coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr,
+ bool is_on_empty, Node *defexpr)
{
+ Node *orig_defexpr = defexpr;
int location;
Oid exprtype;
@@ -4290,63 +4293,31 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
format_type_be(jsexpr->returning->typid)),
parser_errposition(pstate, location)));
- return defexpr;
-}
+ /* Don't allow unsafe DEFAUL ON EMPTY expressions, unless ERROR ON ERROR */
+ if (!is_on_empty ||
+ jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+ return defexpr;
-/*
- * Initialize SQL/JSON item coercion from the SQL type "typid" to the target
- * "returning" type.
- */
-static JsonCoercion *
-initJsonItemCoercion(ParseState *pstate, Oid typid,
- const JsonReturning *returning)
-{
- Node *expr;
+ if (!expr_can_throw_errors(defexpr))
+ return defexpr;
- if (typid == UNKNOWNOID)
+ /* Try to simplify expression if there is non-empty coercion */
+ if (defexpr != orig_defexpr &&
+ !expr_can_throw_errors(orig_defexpr))
{
- expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid);
- }
- else
- {
- CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+ defexpr = eval_const_expressions(NULL, defexpr);
- placeholder->typeId = typid;
- placeholder->typeMod = -1;
- placeholder->collation = InvalidOid;
-
- expr = (Node *) placeholder;
+ if (!expr_can_throw_errors(defexpr))
+ return defexpr;
}
- return coerceJsonExpr(pstate, expr, returning);
-}
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unsafe DEFAULT ON EMPTY expressions are not supported"),
+ errhint("Use ERROR ON ERROR clause or try to simplify expression into constant-like form."),
+ parser_errposition(pstate, location)));
-static void
-initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions,
- const JsonReturning *returning, Oid contextItemTypeId)
-{
- struct
- {
- JsonCoercion **coercion;
- Oid typid;
- } *p,
- coercionTypids[] =
- {
- {&coercions->null, UNKNOWNOID},
- {&coercions->string, TEXTOID},
- {&coercions->numeric, NUMERICOID},
- {&coercions->boolean, BOOLOID},
- {&coercions->date, DATEOID},
- {&coercions->time, TIMEOID},
- {&coercions->timetz, TIMETZOID},
- {&coercions->timestamp, TIMESTAMPOID},
- {&coercions->timestamptz, TIMESTAMPTZOID},
- {&coercions->composite, contextItemTypeId},
- {NULL, InvalidOid}
- };
-
- for (p = coercionTypids; p->coercion; p++)
- *p->coercion = initJsonItemCoercion(pstate, p->typid, returning);
+ return NULL;
}
/*
@@ -4370,17 +4341,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
jsexpr->on_empty->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, true,
jsexpr->on_empty->default_expr);
jsexpr->on_error->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, false,
jsexpr->on_error->default_expr);
- jsexpr->coercions = makeNode(JsonItemCoercions);
- initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning,
- exprType(contextItemExpr));
-
break;
case JSON_QUERY_OP:
@@ -4389,11 +4356,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
transformJsonFuncExprOutput(pstate, func, jsexpr);
jsexpr->on_empty->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, true,
jsexpr->on_empty->default_expr);
jsexpr->on_error->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, false,
jsexpr->on_error->default_expr);
jsexpr->wrapper = func->wrapper;
@@ -4409,6 +4376,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT;
jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
+ /* Coerce intermediate boolean result to the output type if needed */
if (!OidIsValid(jsexpr->returning->typid))
{
jsexpr->returning->typid = BOOLOID;
@@ -4416,32 +4384,21 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
}
else if (jsexpr->returning->typid != BOOLOID)
{
- CaseTestExpr *placeholder = makeNode(CaseTestExpr);
- int location = exprLocation((Node *) jsexpr);
-
- placeholder->typeId = BOOLOID;
- placeholder->typeMod = -1;
- placeholder->collation = InvalidOid;
-
- jsexpr->result_coercion = makeNode(JsonCoercion);
- jsexpr->result_coercion->expr =
- coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID,
- jsexpr->returning->typid,
- jsexpr->returning->typmod,
- COERCION_EXPLICIT,
- COERCE_IMPLICIT_CAST,
- location);
-
- if (!jsexpr->result_coercion->expr)
- ereport(ERROR,
- (errcode(ERRCODE_CANNOT_COERCE),
- errmsg("cannot cast type %s to %s",
- format_type_be(BOOLOID),
- format_type_be(jsexpr->returning->typid)),
- parser_coercion_errposition(pstate, location, (Node *) jsexpr)));
+ switch (jsexpr->returning->typid)
+ {
+ case INT4OID:
+ case TEXTOID:
+ case VARCHAROID:
+ case BPCHAROID:
+ break;
- if (jsexpr->result_coercion->expr == (Node *) placeholder)
- jsexpr->result_coercion->expr = NULL;
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("returning type %s is not supported in JSON_EXISTS()",
+ format_type_be(jsexpr->returning->typid)),
+ parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+ }
}
break;
@@ -4457,7 +4414,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
errmsg("JSON_TABLE() is not yet implemented for the json type"),
errhint("Try casting the argument to jsonb"),
parser_errposition(pstate, func->location)));
-
break;
}
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index da9df4ae766..9c9f9ad703f 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -1318,9 +1318,11 @@ jspIsMutableWalker(JsonPathItem *jpi, JsonPathMutableContext *cxt)
* Check whether jsonpath expression is immutable or not.
*/
bool
-jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
+jspIsMutable(JsonPath *path, List *varnames, List *varexprs,
+ bool *returns_datetime)
{
JsonPathMutableContext cxt;
+ JsonPathDatatypeStatus status;
JsonPathItem jpi;
cxt.varnames = varnames;
@@ -1330,7 +1332,9 @@ jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
cxt.mutable = false;
jspInit(&jpi, path);
- jspIsMutableWalker(&jpi, &cxt);
+ status = jspIsMutableWalker(&jpi, &cxt);
+
+ *returns_datetime = status != jpdsNonDateTime;
return cxt.mutable;
}
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index c8ef917ffe0..adabf57c97b 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -762,43 +762,14 @@ typedef struct JsonExprState
{
JsonExpr *jsexpr; /* original expression node */
- struct
- {
- FmgrInfo func; /* typinput function for output type */
- Oid typioparam;
- } input; /* I/O info for output type */
-
NullableDatum
- *formatted_expr, /* formatted context item value */
- *res_expr, /* result item */
- *coercion_expr, /* input for JSON item coercion */
- *pathspec; /* path specification value */
+ formatted_expr, /* formatted context item value */
+ coercion_expr, /* input for JSON item coercion */
+ pathspec; /* path specification value */
- ExprState *result_expr; /* coerced to output type */
ExprState *default_on_empty; /* ON EMPTY DEFAULT expression */
ExprState *default_on_error; /* ON ERROR DEFAULT expression */
List *args; /* passing arguments */
-
- void *cache; /* cache for json_populate_type() */
-
- struct JsonCoercionsState
- {
- struct JsonCoercionState
- {
- JsonCoercion *coercion; /* coercion expression */
- ExprState *estate; /* coercion expression state */
- } null,
- string,
- numeric ,
- boolean,
- date,
- time,
- timetz,
- timestamp,
- timestamptz,
- composite;
- } coercions; /* states for coercion from SQL/JSON item
- * types directly to the output type */
} JsonExprState;
/* functions in execExpr.c */
@@ -860,18 +831,8 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
ExprContext *econtext, TupleTableSlot *slot);
extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
-extern void ExecEvalJson(ExprState *state, ExprEvalStep *op,
- ExprContext *econtext);
-extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item,
- JsonReturning *returning,
- struct JsonCoercionsState *coercions,
- struct JsonCoercionState **pjcstate);
-extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
- struct JsonCoercionsState *);
-extern Datum ExecEvalExprPassingCaseValue(ExprState *estate,
- ExprContext *econtext, bool *isnull,
- Datum caseval_datum,
- bool caseval_isnull);
+extern void ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup,
ExprContext *aggcontext);
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 3aa96bb6855..14fbb32ce9c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1551,39 +1551,6 @@ typedef struct JsonBehavior
Node *default_expr; /* default expression, if any */
} JsonBehavior;
-/*
- * JsonCoercion -
- * coercion from SQL/JSON item types to SQL types
- */
-typedef struct JsonCoercion
-{
- NodeTag type;
- Node *expr; /* resulting expression coerced to target type */
- bool via_populate; /* coerce result using json_populate_type()? */
- bool via_io; /* coerce result using type input function? */
- Oid collation; /* collation for coercion via I/O or populate */
-} JsonCoercion;
-
-/*
- * JsonItemCoercions -
- * expressions for coercion from SQL/JSON item types directly to the
- * output SQL type
- */
-typedef struct JsonItemCoercions
-{
- NodeTag type;
- JsonCoercion *null;
- JsonCoercion *string;
- JsonCoercion *numeric;
- JsonCoercion *boolean;
- JsonCoercion *date;
- JsonCoercion *time;
- JsonCoercion *timetz;
- JsonCoercion *timestamp;
- JsonCoercion *timestamptz;
- JsonCoercion *composite; /* arrays and objects */
-} JsonItemCoercions;
-
/*
* JsonExpr -
* transformed representation of JSON_VALUE(), JSON_QUERY(), JSON_EXISTS()
@@ -1593,7 +1560,6 @@ typedef struct JsonExpr
Expr xpr;
JsonExprOp op; /* json function ID */
Node *formatted_expr; /* formatted context item expression */
- JsonCoercion *result_coercion; /* resulting coercion to RETURNING type */
JsonFormat *format; /* context item format (JSON/JSONB) */
Node *path_spec; /* JSON path specification expression */
List *passing_names; /* PASSING argument names */
@@ -1601,8 +1567,8 @@ typedef struct JsonExpr
JsonReturning *returning; /* RETURNING clause type/format info */
JsonBehavior *on_empty; /* ON EMPTY behavior */
JsonBehavior *on_error; /* ON ERROR behavior */
- JsonItemCoercions *coercions; /* coercions for JSON_VALUE */
JsonWrapper wrapper; /* WRAPPER for JSON_QUERY */
+ Oid collation; /* OID of collation, or InvalidOid if none */
bool omit_quotes; /* KEEP/OMIT QUOTES for JSON_QUERY */
int location; /* token location, or -1 if unknown */
} JsonExpr;
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 6c5203dc448..4d61b88c161 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -55,4 +55,6 @@ extern Query *inline_set_returning_function(PlannerInfo *root,
extern Bitmapset *pull_paramids(Expr *expr);
+extern bool expr_can_throw_errors(Node *expr);
+
#endif /* CLAUSES_H */
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 8e79b8dc9f0..e9134b7eab4 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -177,7 +177,8 @@ extern bool jspGetBool(JsonPathItem *v);
extern char *jspGetString(JsonPathItem *v, int32 *len);
extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
JsonPathItem *to, int i);
-extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs);
+extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs,
+ bool *returns_datetime);
extern const char *jspOperationName(JsonPathItemType type);
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index ef496110af3..0e07bbc858f 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -184,11 +184,11 @@ SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
(1 row)
SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
-ERROR: cannot cast type boolean to jsonb
+ERROR: returning type jsonb is not supported in JSON_EXISTS()
LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
^
SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
-ERROR: cannot cast type boolean to real
+ERROR: returning type real is not supported in JSON_EXISTS()
LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
^
-- JSON_VALUE
@@ -242,7 +242,9 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
/* jsonb bytea ??? */
SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
-ERROR: SQL/JSON item cannot be cast to target type
+ERROR: returning type bytea is not supported in JSON_VALUE()
+LINE 2: SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ...
+ ^
SELECT JSON_VALUE(jsonb '1.23', '$');
json_value
------------
@@ -349,14 +351,129 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
03-01-2017
(1 row)
--- Test NULL checks execution in domain types
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float4);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float4);
+ json_value
+------------
+ NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4);
+ json_value
+------------
+ -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR);
+ERROR: invalid input syntax for type real: "err"
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float8);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float8);
+ json_value
+------------
+ NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8);
+ json_value
+------------
+ -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR);
+ERROR: invalid input syntax for type double precision: "err"
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING numeric);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING numeric);
+ json_value
+------------
+ NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric);
+ json_value
+------------
+ -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR);
+ERROR: invalid input syntax for type numeric: "err"
+-- Test for domain types
CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
+CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0');
+CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0');
+-- Test casting to json[b] domains (items casted as is, strings are not unquoted)
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null);
+ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_n...
+ ^
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_...
+ ^
+-- Test NULL checks execution in domain types
SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
-ERROR: domain sqljsonb_int_not_null does not allow null values
+ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+ ^
SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
-ERROR: domain sqljsonb_int_not_null does not allow null values
+ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+ ^
SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
-ERROR: domain sqljsonb_int_not_null does not allow null values
+ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+ ^
+-- Test returning of non-scalar items
SELECT JSON_VALUE(jsonb '[]', '$');
json_value
------------
@@ -478,11 +595,9 @@ SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
(1 row)
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
- json_value
-------------
- (1,2)
-(1 row)
-
+ERROR: returning type point is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )...
+ ^
-- Test timestamptz passing and output
SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
json_value
@@ -514,6 +629,36 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
"2018-02-21T02:34:56+00:00"
(1 row)
+-- test errors in DEFAULT ON EMPTY expressions
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int
+ DEFAULT 1 / x ON EMPTY
+ DEFAULT 2 ON ERROR)
+FROM (VALUES (1::int), (0)) x(x);
+ERROR: unsafe DEFAULT ON EMPTY expressions are not supported
+LINE 2: DEFAULT 1 / x ON EMPTY
+ ^
+HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form.
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY);
+ERROR: invalid input syntax for type integer: "err"
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON E...
+ ^
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY);
+ERROR: unsafe DEFAULT ON EMPTY expressions are not supported
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ...
+ ^
+HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form.
-- JSON_QUERY
SELECT
JSON_QUERY(js, '$'),
@@ -852,49 +997,35 @@ FROM
CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
- json_query
------------------------------------------------------
- (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",)
-(1 row)
-
+ERROR: returning type sqljsonb_rec is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "...
+ ^
SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
- unnest
-------------------------
- {"a": 1, "b": ["foo"]}
- {"a": 2, "c": {}}
- 123
-(3 rows)
-
+ERROR: returning type sqljsonb_rec is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "...
+ ^
SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
- a | t | js | jb | jsa
----+-------------+----+------------+-----
- 1 | ["foo", []] | | |
- 2 | | | [{}, true] |
-(2 rows)
-
+ERROR: returning type sqljsonb_reca is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "...
+ ^
-- Extension: array types returning
SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
- json_query
---------------
- {1,2,NULL,3}
-(1 row)
-
+ERROR: returning type integer[] is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING i...
+ ^
SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
- a | t | js | jb | jsa
----+-------------+----+------------+-----
- 1 | ["foo", []] | | |
- 2 | | | [{}, true] |
-(2 rows)
-
+ERROR: returning type sqljsonb_rec[] is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo"...
+ ^
-- Extension: domain types returning
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
- json_query
-------------
- 1
-(1 row)
-
+ERROR: returning type sqljsonb_int_not_null is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb...
+ ^
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
-ERROR: domain sqljsonb_int_not_null does not allow null values
+ERROR: returning type sqljsonb_int_not_null is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb...
+ ^
-- Test timestamptz passing and output
SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
json_query
@@ -1019,6 +1150,25 @@ ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable
DROP TABLE test_jsonb_mutability;
-- JSON_TABLE
-- Should fail (JSON_TABLE can be used only in FROM clause)
@@ -1048,7 +1198,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$'
(1 row)
-- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
SELECT *
FROM
(VALUES
@@ -1069,7 +1218,6 @@ FROM
"char(4)" char(4) PATH '$',
"bool" bool PATH '$',
"numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
@@ -1085,29 +1233,26 @@ FROM
exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR,
js2 json PATH '$',
jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$'
+ jsb2q jsonb PATH '$' OMIT QUOTES
)
) jt
ON true;
- js | id | id2 | int | text | char(4) | bool | numeric | domain | js | jb | jst | jsc | jsv | jsb | jsbq | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 | js2 | jsb2w | jsb2q | ia | ta | jba
----------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------+----+----+-----
- 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | |
- [] | | | | | | | | | | | | | | | | | | | | | | | | | | |
- {} | 1 | 1 | | | | | | | {} | {} | {} | {} | {} | {} | {} | | | f | 0 | | false | {} | [{}] | {} | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | | f | 0 | | false | 1.23 | [1.23] | 1.23 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | 2 | "2" | "2" | "2" | "2" | "2" | "2" | 2 | | | f | 0 | | false | "2" | ["2"] | 2 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | aaaaaaa | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | | | f | 0 | | false | "aaaaaaa" | ["aaaaaaa"] | | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | foo | foo | | | | "foo" | "foo" | "foo" | "foo | "foo | "foo" | | | | f | 0 | | false | "foo" | ["foo"] | | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | | | | | | | null | null | null | null | null | null | null | | | f | 0 | | false | null | [null] | null | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 0 | false | fals | f | | false | false | false | false | fals | fals | false | false | | | f | 0 | | false | false | [false] | false | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | 1 | true | true | t | | true | true | true | true | true | true | true | true | | | f | 0 | | false | true | [true] | true | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | | | | | | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 | 123 | t | 1 | 1 | true | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | [1,2] | [1,2 | | | [1,2] | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] | | | f | 0 | | false | "[1,2]" | ["[1,2]"] | [1, 2] | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | 11 | | "str" | "str | | | "str" | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" | | | f | 0 | | false | "\"str\"" | ["\"str\""] | "str" | | |
+ js | id | id2 | int | text | char(4) | bool | numeric | js | jb | jst | jsc | jsv | jsb | jsbq | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 | js2 | jsb2w | jsb2q
+---------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------
+ 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1
+ [] | | | | | | | | | | | | | | | | | | | | | | |
+ {} | 1 | 1 | | | | | | {} | {} | {} | {} | {} | {} | {} | | | f | 0 | | false | {} | [{}] | {}
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | | f | 0 | | false | 1.23 | [1.23] | 1.23
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | "2" | "2" | "2" | "2" | "2" | "2" | 2 | | | f | 0 | | false | "2" | ["2"] | 2
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | | | f | 0 | | false | "aaaaaaa" | ["aaaaaaa"] |
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | foo | foo | | | "foo" | "foo" | "foo" | "foo | "foo | "foo" | | | | f | 0 | | false | "foo" | ["foo"] |
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | | | | | | null | null | null | null | null | null | null | | | f | 0 | | false | null | [null] | null
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 0 | false | fals | f | | false | false | false | fals | fals | false | false | | | f | 0 | | false | false | [false] | false
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | 1 | true | true | t | | true | true | true | true | true | true | true | | | f | 0 | | false | true | [true] | true
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | | | | | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 | 123 | t | 1 | 1 | true | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123}
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | [1,2] | [1,2 | | | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] | | | f | 0 | | false | "[1,2]" | ["[1,2]"] | [1, 2]
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | 11 | | "str" | "str | | | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" | | | f | 0 | | false | "\"str\"" | ["\"str\""] | "str"
(14 rows)
-- JSON_TABLE: Test backward parsing
@@ -1123,7 +1268,6 @@ SELECT * FROM
"char(4)" char(4) PATH '$',
"bool" bool PATH '$',
"numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
@@ -1139,9 +1283,6 @@ SELECT * FROM
js2 json PATH '$',
jsb2w jsonb PATH '$' WITH WRAPPER,
jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
NESTED PATH '$[1]' AS p1 COLUMNS (
a1 int,
NESTED PATH '$[*]' AS "p1 1" COLUMNS (
@@ -1168,7 +1309,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
"json_table"."char(4)",
"json_table".bool,
"json_table"."numeric",
- "json_table".domain,
"json_table".js,
"json_table".jb,
"json_table".jst,
@@ -1184,9 +1324,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
"json_table".js2,
"json_table".jsb2w,
"json_table".jsb2q,
- "json_table".ia,
- "json_table".ta,
- "json_table".jba,
"json_table".a1,
"json_table".b1,
"json_table".a11,
@@ -1205,7 +1342,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
"char(4)" character(4) PATH '$',
bool boolean PATH '$',
"numeric" numeric PATH '$',
- domain jsonb_test_domain PATH '$',
js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
@@ -1221,9 +1357,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
js2 json PATH '$',
jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER,
jsb2q jsonb PATH '$' OMIT QUOTES,
- ia integer[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
NESTED PATH '$[1]' AS p1
COLUMNS (
a1 integer PATH '$."a1"',
@@ -1248,15 +1381,14 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
- Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
+ Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
(3 rows)
DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
-- JSON_TABLE: ON EMPTY/ON ERROR behavior
SELECT *
FROM
@@ -1318,15 +1450,15 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
(1 row)
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to smallint
+ERROR: returning type smallint is not supported in JSON_EXISTS()
LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXI...
^
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to bigint
+ERROR: returning type bigint is not supported in JSON_EXISTS()
LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXI...
^
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to real
+ERROR: returning type real is not supported in JSON_EXISTS()
LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 E...
^
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
@@ -1336,11 +1468,11 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'))
(1 row)
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to json
+ERROR: returning type json is not supported in JSON_EXISTS()
LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXI...
^
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to jsonb
+ERROR: returning type jsonb is not supported in JSON_EXISTS()
LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX...
^
-- JSON_TABLE: nested paths and plans
@@ -2102,11 +2234,14 @@ set parallel_leader_participation = off;
-- Should be non-parallel due to subtransactions
EXPLAIN (COSTS OFF)
SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
- QUERY PLAN
----------------------------------------------
- Aggregate
- -> Seq Scan on test_parallel_jsonb_value
-(2 rows)
+ QUERY PLAN
+------------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 4
+ -> Partial Aggregate
+ -> Parallel Seq Scan on test_parallel_jsonb_value
+(5 rows)
SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
sum
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index fff25374808..43a194ad09f 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -82,12 +82,43 @@ SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
--- Test NULL checks execution in domain types
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR);
+
+-- Test for domain types
CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
+CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0');
+CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0');
+
+-- Test casting to json[b] domains (items casted as is, strings are not unquoted)
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null);
+
+-- Test NULL checks execution in domain types
SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
+
+-- Test returning of non-scalar items
SELECT JSON_VALUE(jsonb '[]', '$');
SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
SELECT JSON_VALUE(jsonb '{}', '$');
@@ -133,6 +164,17 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+-- test errors in DEFAULT ON EMPTY expressions
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int
+ DEFAULT 1 / x ON EMPTY
+ DEFAULT 2 ON ERROR)
+FROM (VALUES (1::int), (0)) x(x);
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY);
+
-- JSON_QUERY
SELECT
@@ -318,6 +360,21 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ?
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable
+
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable
+
DROP TABLE test_jsonb_mutability;
-- JSON_TABLE
@@ -338,8 +395,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$'
COLUMNS (item int PATH '$', foo int)) bar;
-- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
-
SELECT *
FROM
(VALUES
@@ -360,7 +415,6 @@ FROM
"char(4)" char(4) PATH '$',
"bool" bool PATH '$',
"numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
@@ -377,10 +431,7 @@ FROM
js2 json PATH '$',
jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$'
+ jsb2q jsonb PATH '$' OMIT QUOTES
)
) jt
ON true;
@@ -399,7 +450,6 @@ SELECT * FROM
"char(4)" char(4) PATH '$',
"bool" bool PATH '$',
"numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
@@ -416,9 +466,6 @@ SELECT * FROM
js2 json PATH '$',
jsb2w jsonb PATH '$' WITH WRAPPER,
jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
NESTED PATH '$[1]' AS p1 COLUMNS (
a1 int,
@@ -443,7 +490,6 @@ SELECT * FROM
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
-- JSON_TABLE: ON EMPTY/ON ERROR behavior
SELECT *
--
2.17.1
v9-0008-Remove-subsidary-ExprStates-in-SQL-JSON-execution.patch (text/x-patch, 22.5K) download
From 003bfccffc69824e0fb947d11f3f75a7b70fefb4 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Mon, 29 Aug 2022 22:41:08 +0300
Subject: [PATCH v9 8/9] Remove subsidary ExprStates in SQL/JSON execution
---
src/backend/executor/execExpr.c | 88 ++++++++---
src/backend/executor/execExprInterp.c | 206 ++++++++++++--------------
src/backend/jit/llvm/llvmjit_expr.c | 70 ++++++++-
src/backend/jit/llvm/llvmjit_types.c | 1 +
src/backend/utils/adt/jsonpath_exec.c | 79 +++++++++-
src/include/executor/execExpr.h | 20 ++-
src/include/utils/jsonpath.h | 4 -
7 files changed, 317 insertions(+), 151 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f50e0d703f8..68227ebee21 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4159,9 +4159,16 @@ static void
ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
Datum *resv, bool *resnull)
{
+ ExprEvalStep *step;
JsonExprState *jsestate;
ListCell *argexprlc;
ListCell *argnamelc;
+ int skip_step_off;
+ int execpath_step_off;
+ int done_step_off;
+ int default_on_empty_step_off = -1;
+ int default_on_error_step_off = -1;
+ int default_on_empty_jump_off = -1;
/* JSON_TABLE preudo-function returns context item as a result */
if (jexpr->op == JSON_TABLE_OP)
@@ -4174,9 +4181,6 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
jsestate = palloc0(sizeof(JsonExprState));
jsestate->jsexpr = jexpr;
- scratch->opcode = EEOP_JSONEXPR;
- scratch->d.jsonexpr.jsestate = jsestate;
-
ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
&jsestate->formatted_expr.value,
&jsestate->formatted_expr.isnull);
@@ -4185,15 +4189,12 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
&jsestate->pathspec.value,
&jsestate->pathspec.isnull);
- if (jexpr->on_empty)
- jsestate->default_on_empty =
- ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
- state->parent);
-
- jsestate->default_on_error =
- ExecInitExpr((Expr *) jexpr->on_error->default_expr,
- state->parent);
+ scratch->opcode = EEOP_JSONEXPR_SKIP;
+ scratch->d.jsonexpr_skip.jsestate = jsestate;
+ skip_step_off = state->steps_len;
+ ExprEvalPushStep(state, scratch);
+ /* PASSING args. */
jsestate->args = NIL;
forboth(argexprlc, jexpr->passing_values,
@@ -4206,16 +4207,69 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
var->name = pstrdup(argname->sval);
var->typid = exprType((Node *) argexpr);
var->typmod = exprTypmod((Node *) argexpr);
- var->estate = ExecInitExpr(argexpr, state->parent);
+
+ /*
+ * A separate ExprState is not necessary for these expressions
+ * when being evaluated for a JsonExpr, like in this case,
+ * because they will evaluated as the steps of the JsonExpr.
+ */
+ var->estate = NULL;
var->econtext = NULL;
var->mcxt = NULL;
- var->evaluated = false;
- var->value = (Datum) 0;
- var->isnull = true;
- jsestate->args =
- lappend(jsestate->args, var);
+ /*
+ * Mark these as always evaluated because they must have been
+ * evaluated before JSON path evaluation begins, because we
+ * haven't pushed the step for the latter yet.
+ */
+ var->evaluated = true;
+
+ ExecInitExprRec((Expr *) argexpr, state, &var->value, &var->isnull);
+
+ jsestate->args = lappend(jsestate->args, var);
}
+ /* Step for the actual JSON path evaluation. */
+ scratch->opcode = EEOP_JSONEXPR_PATH;
+ scratch->d.jsonexpr.jsestate = jsestate;
+ execpath_step_off = state->steps_len;
ExprEvalPushStep(state, scratch);
+
+ if (jexpr->on_empty && jexpr->on_empty->default_expr)
+ {
+ default_on_empty_step_off = state->steps_len;
+ ExecInitExprRec((Expr *) jexpr->on_empty->default_expr, state, resv, resnull);
+
+ scratch->opcode = EEOP_JUMP;
+ default_on_empty_jump_off = state->steps_len;
+ ExprEvalPushStep(state, scratch);
+ }
+
+ if (jexpr->on_error->default_expr)
+ {
+ default_on_error_step_off = state->steps_len;
+ ExecInitExprRec((Expr *) jexpr->on_error->default_expr, state, resv, resnull);
+ }
+
+ /* Adjust jump locations */
+ done_step_off = state->steps_len;
+
+ /* EEOP_JSONEXPR_SKIP */
+ step = &state->steps[skip_step_off];
+ step->d.jsonexpr_skip.jump_done = done_step_off;
+
+ /* EEOP_JSONEXPR_PATH */
+ step = &state->steps[execpath_step_off];
+ step->d.jsonexpr.jump_done = done_step_off;
+ step->d.jsonexpr.jump_default_on_empty =
+ default_on_empty_step_off > 0 ? default_on_empty_step_off : done_step_off;
+ step->d.jsonexpr.jump_default_on_error =
+ default_on_error_step_off > 0 ? default_on_error_step_off : done_step_off;
+
+ /* EEOP_JUMP */
+ if (default_on_empty_jump_off > 0)
+ {
+ step = &state->steps[default_on_empty_jump_off];
+ step->d.jump.jumpdone = done_step_off;
+ }
}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 45c648817ba..d39223e0d15 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -491,7 +491,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_SUBPLAN,
&&CASE_EEOP_JSON_CONSTRUCTOR,
&&CASE_EEOP_IS_JSON,
- &&CASE_EEOP_JSONEXPR,
+ &&CASE_EEOP_JSONEXPR_SKIP,
+ &&CASE_EEOP_JSONEXPR_PATH,
&&CASE_EEOP_AGG_STRICT_DESERIALIZE,
&&CASE_EEOP_AGG_DESERIALIZE,
&&CASE_EEOP_AGG_STRICT_INPUT_CHECK_ARGS,
@@ -1842,11 +1843,26 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
- EEO_CASE(EEOP_JSONEXPR)
+ EEO_CASE(EEOP_JSONEXPR_SKIP)
{
/* too complex for an inline implementation */
- ExecEvalJsonExpr(state, op, econtext);
- EEO_NEXT();
+ if (ExecEvalJsonExprSkip(state, op))
+ EEO_JUMP(op->d.jsonexpr_skip.jump_done);
+ else
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_JSONEXPR_PATH)
+ {
+ /* too complex for an inline implementation */
+ int res = ExecEvalJsonExpr(state, op);
+
+ if (res == 0)
+ EEO_JUMP(op->d.jsonexpr.jump_default_on_empty);
+ else if (res == -1)
+ EEO_JUMP(op->d.jsonexpr.jump_default_on_error);
+ else
+ EEO_JUMP(op->d.jsonexpr.jump_done);
}
EEO_CASE(EEOP_LAST)
@@ -4701,59 +4717,6 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
*op->resnull = isnull;
}
-/*
- * Evaluate a JSON path variable caching computed value.
- */
-int
-EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
- JsonbValue *val, JsonbValue *baseObject)
-{
- JsonPathVariableEvalContext *var = NULL;
- List *vars = cxt;
- ListCell *lc;
- int id = 1;
-
- if (!varName)
- return list_length(vars);
-
- foreach(lc, vars)
- {
- var = lfirst(lc);
-
- if (!strncmp(var->name, varName, varNameLen))
- break;
-
- var = NULL;
- id++;
- }
-
- if (!var)
- return -1;
-
- if (!var->evaluated)
- {
- MemoryContext oldcxt = var->mcxt ?
- MemoryContextSwitchTo(var->mcxt) : NULL;
-
- var->value = ExecEvalExpr(var->estate, var->econtext, &var->isnull);
- var->evaluated = true;
-
- if (oldcxt)
- MemoryContextSwitchTo(oldcxt);
- }
-
- if (var->isnull)
- {
- val->type = jbvNull;
- return 0;
- }
-
- JsonItemFromDatum(var->value, var->typid, var->typmod, val);
-
- *baseObject = *val;
- return id;
-}
-
/*
* Check whether we need to override default coercion in
* JSON_QUERY(OMIT QUOTES) case.
@@ -5210,9 +5173,8 @@ ExecJsonValueCoercion(JsonbValue *item, Oid typid, int32 typmod,
* type.
*/
static Datum
-ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
- ExprState *default_estate,
- Oid ret_typid, int32 ret_typmod, bool *is_null)
+ExecEvalJsonBehavior(JsonBehavior *behavior, Oid ret_typid,
+ int32 ret_typmod, bool *is_null)
{
*is_null = false;
@@ -5262,12 +5224,8 @@ ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
return (Datum) 0;
case JSON_BEHAVIOR_DEFAULT:
- /*
- * Execute DEFAULT expression.
- * Coercion is not needed here, because expression is
- * already coerced to the target type by the parser.
- */
- return ExecEvalExpr(default_estate, econtext, is_null);
+ Assert(0);
+ return (Datum) 0; /* must be handled by caller */
default:
elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
@@ -5276,28 +5234,27 @@ ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
}
static Datum
-ExecEvalJsonExprInternal(ExprState *state,
- JsonExprState *jsestate, ExprContext *econtext,
+ExecEvalJsonExprInternal(ExprState *state, JsonExprState *jsestate,
JsonPath *path, Datum item, bool *resnull,
- bool *error)
+ bool *empty, bool *error)
{
JsonExpr *jexpr = jsestate->jsexpr;
- bool empty = false;
Datum res = (Datum) 0;
Oid ret_typid = jexpr->returning->typid;
int32 ret_typmod = jexpr->returning->typmod;
*resnull = true;
+ *empty = false;
switch (jexpr->op)
{
case JSON_QUERY_OP:
- res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error,
+ res = JsonPathQuery(item, path, jexpr->wrapper, empty, error,
jsestate->args);
if (error && *error)
return (Datum) 0;
- if (empty)
+ if (*empty)
break;
*resnull = !DatumGetPointer(res);
@@ -5364,13 +5321,13 @@ ExecEvalJsonExprInternal(ExprState *state,
case JSON_VALUE_OP:
{
- JsonbValue *jbv = JsonPathValue(item, path, &empty, error,
+ JsonbValue *jbv = JsonPathValue(item, path, empty, error,
jsestate->args);
if (error && *error)
return (Datum) 0;
- if (empty)
+ if (*empty)
break;
if (!jbv)
@@ -5408,78 +5365,101 @@ ExecEvalJsonExprInternal(ExprState *state,
return (Datum) 0;
}
- Assert(empty);
- Assert(jexpr->on_empty); /* it is not JSON_EXISTS */
+ Assert(*empty);
- if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
- {
- if (error)
- {
- *error = true;
- return (Datum) 0;
- }
+ return (Datum) 0;
+}
- ereport(ERROR,
- (errcode(ERRCODE_NO_SQL_JSON_ITEM),
- errmsg("no SQL/JSON item")));
+bool
+ExecEvalJsonExprSkip(ExprState *state, ExprEvalStep *op)
+{
+ JsonExprState *jsestate = op->d.jsonexpr_skip.jsestate;
+
+ /*
+ * Skip if either of the input expressions has turned out to be
+ * NULL, though do execute domain checks for NULLs, which are
+ * handled by the coercion step.
+ */
+ if (jsestate->formatted_expr.isnull ||
+ jsestate->pathspec.isnull)
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+ return true;
}
- /* Execute ON EMPTY behavior */
- return ExecEvalJsonBehavior(econtext, jexpr->on_empty,
- jsestate->default_on_empty,
- ret_typid, ret_typmod, resnull);
+ /*
+ * Go evaluate the PASSING args if any and subsequently JSON path
+ * itself.
+ */
+ return false;
}
/* ----------------------------------------------------------------
* ExecEvalJsonExpr
* ----------------------------------------------------------------
+ *
+ * Return value:
+ * 1 - Ok, jump to the end of JsonExpr
+ * 0 - empty result, need to execute DEFAULT ON EMPTY expression
+ * -1 - error occured, need to execute DEFAULT ON ERROR expression
*/
-void
-ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+int
+ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op)
{
JsonExprState *jsestate = op->d.jsonexpr.jsestate;
JsonExpr *jexpr = jsestate->jsexpr;
Datum item;
Datum res;
JsonPath *path;
- ListCell *lc;
+ bool empty = false;
bool error = false;
- bool throwErrors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR;
-
- if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull)
- {
- *op->resnull = true;
- *op->resvalue = (Datum) 0;
- return;
- }
+ bool throw_errors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR;
item = jsestate->formatted_expr.value;
path = DatumGetJsonPathP(jsestate->pathspec.value);
- /* reset JSON path variable contexts */
- foreach(lc, jsestate->args)
+ res = ExecEvalJsonExprInternal(state, jsestate, path, item,
+ op->resnull, &empty,
+ throw_errors ? NULL : &error);
+
+ if (empty && !error)
{
- JsonPathVariableEvalContext *var = lfirst(lc);
+ Assert(jexpr->on_empty); /* it is not JSON_EXISTS */
- var->econtext = econtext;
- var->evaluated = false;
- }
+ if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+ {
+ if (throw_errors)
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_SQL_JSON_ITEM),
+ errmsg("no SQL/JSON item")));
- res = ExecEvalJsonExprInternal(state, jsestate, econtext,
- path, item, op->resnull,
- throwErrors ? NULL : &error);
+ error = true; /* fallback to error behavior */
+ }
+ else if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT)
+ return 0; /* jump to ON EMPTY expression */
+ else
+ /* Execute ON EMPTY behavior */
+ res = ExecEvalJsonBehavior(jexpr->on_empty,
+ jexpr->returning->typid,
+ jexpr->returning->typmod,
+ op->resnull);
+ }
if (error)
{
- Assert(!throwErrors);
+ Assert(!throw_errors);
+
+ if (jexpr->on_error->btype == JSON_BEHAVIOR_DEFAULT)
+ return -1; /* jump to ON ERROR expression */
/* Execute ON ERROR behavior */
- res = ExecEvalJsonBehavior(econtext, jexpr->on_error,
- jsestate->default_on_error,
+ res = ExecEvalJsonBehavior(jexpr->on_error,
jexpr->returning->typid,
jexpr->returning->typmod,
op->resnull);
}
*op->resvalue = res;
+ return 1; /* jump to the end of expression */
}
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index fd72630f5e6..7681a77cd63 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2407,11 +2407,71 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
- case EEOP_JSONEXPR:
- build_EvalXFunc(b, mod, "ExecEvalJsonExpr",
- v_state, op, v_econtext);
- LLVMBuildBr(b, opblocks[opno + 1]);
- break;
+ case EEOP_JSONEXPR_SKIP:
+ {
+ LLVMValueRef v_ret;
+
+ /*
+ * Call ExecEvalJsonExprSkip() to decide if JSON
+ * path evaluation can be skipped. This returns
+ * boolean "skip" flag.
+ */
+ v_ret = build_EvalXFunc(b, mod, "ExecEvalJsonExprSkip",
+ v_state, op);
+ v_ret = LLVMBuildZExt(b, v_ret, TypeStorageBool, "");
+
+ /*
+ * Jump to coercion step if true was returned,
+ * which signifies skipping of JSON path evaluation,
+ * else to the next step which must point to the
+ * steps to evaluate PASSING args, if any, or to
+ * the JSON path evaluation.
+ */
+ LLVMBuildCondBr(b,
+ LLVMBuildICmp(b,
+ LLVMIntEQ,
+ v_ret,
+ l_sbool_const(0),
+ ""),
+ opblocks[opno + 1],
+ opblocks[op->d.jsonexpr_skip.jump_done]);
+ break;
+ }
+
+ case EEOP_JSONEXPR_PATH:
+ {
+ LLVMValueRef v_ret;
+ LLVMValueRef v_switch;
+ int n_jumps = 0;
+
+ v_ret = build_EvalXFunc(b, mod, "ExecEvalJsonExpr",
+ v_state, op);
+
+ if (op->d.jsonexpr.jump_default_on_empty != op->d.jsonexpr.jump_done)
+ n_jumps++;
+
+ if (op->d.jsonexpr.jump_default_on_error != op->d.jsonexpr.jump_done)
+ n_jumps++;
+
+ if (!n_jumps)
+ LLVMBuildBr(b, opblocks[op->d.jsonexpr.jump_done]);
+ else
+ {
+ v_switch = LLVMBuildSwitch(b, v_ret,
+ opblocks[op->d.jsonexpr.jump_done],
+ n_jumps);
+
+ if (op->d.jsonexpr.jump_default_on_empty != op->d.jsonexpr.jump_done)
+ LLVMAddCase(v_switch, l_int32_const(0),
+ opblocks[op->d.jsonexpr.jump_default_on_empty]);
+
+ if (op->d.jsonexpr.jump_default_on_error != op->d.jsonexpr.jump_done)
+ LLVMAddCase(v_switch, l_int32_const(-1),
+ opblocks[op->d.jsonexpr.jump_default_on_error]);
+ }
+
+ break;
+ }
case EEOP_LAST:
Assert(false);
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index 37fe64654b6..b9148ce515c 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -136,6 +136,7 @@ void *referenced_functions[] =
ExecEvalJsonConstructor,
ExecEvalJsonIsPredicate,
ExecEvalJsonExpr,
+ ExecEvalJsonExprSkip,
MakeExpandedObjectReadOnlyInternal,
slot_getmissingattrs,
slot_getsomeattrs_int,
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 5b6a4805721..b8949789710 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -286,6 +286,8 @@ static JsonPathExecResult appendBoolResult(JsonPathExecContext *cxt,
JsonPathItem *jsp, JsonValueList *found, JsonPathBool res);
static void getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
JsonbValue *value);
+static int evalJsonPathVariable(void *cxt, char *varName, int varNameLen,
+ JsonbValue *val, JsonbValue *baseObject);
static void getJsonPathVariable(JsonPathExecContext *cxt,
JsonPathItem *variable, JsonbValue *value);
static int getJsonPathVariableFromJsonb(void *varsJsonb, char *varName,
@@ -2172,6 +2174,71 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item,
}
}
+/*
+ * Evaluate a JSON path variable caching computed value.
+ */
+static int
+evalJsonPathVariable(void *cxt, char *varName, int varNameLen,
+ JsonbValue *val, JsonbValue *baseObject)
+{
+ JsonPathVariableEvalContext *var = NULL;
+ List *vars = cxt;
+ ListCell *lc;
+ int id = 1;
+
+ if (!varName)
+ return list_length(vars);
+
+ foreach(lc, vars)
+ {
+ var = lfirst(lc);
+
+ if (!strncmp(var->name, varName, varNameLen))
+ break;
+
+ var = NULL;
+ id++;
+ }
+
+ if (!var)
+ return -1;
+
+ /*
+ * When belonging to a JsonExpr, path variables are computed with the
+ * JsonExpr's ExprState (var->estate is NULL), so don't need to be computed
+ * here. In some other cases, such as when the path variables belonging
+ * to a JsonTable instead, those variables must be evaluated on their own,
+ * without the enclosing JsonExpr itself needing to be evaluated, so must
+ * be handled here.
+ */
+ if (!var->estate)
+ {
+ Assert(var->evaluated);
+ }
+ else if (!var->evaluated)
+ {
+ MemoryContext oldcxt = var->mcxt ? MemoryContextSwitchTo(var->mcxt) : NULL;
+
+ Assert(var->econtext);
+ var->value = ExecEvalExpr(var->estate, var->econtext, &var->isnull);
+ var->evaluated = true;
+
+ if (oldcxt)
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ if (var->isnull)
+ {
+ val->type = jbvNull;
+ return 0;
+ }
+
+ JsonItemFromDatum(var->value, var->typid, var->typmod, val);
+
+ *baseObject = *val;
+ return id;
+}
+
/*
* Get the value of variable passed to jsonpath executor
*/
@@ -2903,7 +2970,7 @@ compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
bool
JsonPathExists(Datum jb, JsonPath *jp, List *vars, bool *error)
{
- JsonPathExecResult res = executeJsonPath(jp, vars, EvalJsonPathVar,
+ JsonPathExecResult res = executeJsonPath(jp, vars, evalJsonPathVariable,
DatumGetJsonbP(jb), !error, NULL,
true);
@@ -2925,8 +2992,8 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
JsonPathExecResult res PG_USED_FOR_ASSERTS_ONLY;
int count;
- res = executeJsonPath(jp, vars, EvalJsonPathVar, DatumGetJsonbP(jb), !error,
- &found, true);
+ res = executeJsonPath(jp, vars, evalJsonPathVariable,
+ DatumGetJsonbP(jb), !error, &found, true);
Assert(error || !jperIsError(res));
@@ -2992,8 +3059,8 @@ JsonPathValue(Datum jb, JsonPath *jp, bool *empty, bool *error, List *vars)
JsonPathExecResult jper PG_USED_FOR_ASSERTS_ONLY;
int count;
- jper = executeJsonPath(jp, vars, EvalJsonPathVar, DatumGetJsonbP(jb), !error,
- &found, true);
+ jper = executeJsonPath(jp, vars, evalJsonPathVariable,
+ DatumGetJsonbP(jb), !error, &found, true);
Assert(error || !jperIsError(jper));
@@ -3306,7 +3373,7 @@ JsonTableResetContextItem(JsonTableScanState *scan, Datum item)
oldcxt = MemoryContextSwitchTo(scan->mcxt);
- res = executeJsonPath(scan->path, scan->args, EvalJsonPathVar, js,
+ res = executeJsonPath(scan->path, scan->args, evalJsonPathVariable, js,
scan->errorOnError, &scan->found, false /* FIXME */ );
MemoryContextSwitchTo(oldcxt);
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index adabf57c97b..b005bf440dd 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -244,7 +244,8 @@ typedef enum ExprEvalOp
EEOP_SUBPLAN,
EEOP_JSON_CONSTRUCTOR,
EEOP_IS_JSON,
- EEOP_JSONEXPR,
+ EEOP_JSONEXPR_SKIP,
+ EEOP_JSONEXPR_PATH,
/* aggregation related nodes */
EEOP_AGG_STRICT_DESERIALIZE,
@@ -696,8 +697,18 @@ typedef struct ExprEvalStep
struct
{
struct JsonExprState *jsestate;
+ int jump_done;
+ int jump_default_on_empty; /* ON EMPTY DEFAULT expression */
+ int jump_default_on_error; /* ON ERROR DEFAULT expression */
} jsonexpr;
+ /* for EEOP_JSONEXPR_SKIP */
+ struct
+ {
+ struct JsonExprState *jsestate;
+ int jump_done;
+ } jsonexpr_skip;
+
} d;
} ExprEvalStep;
@@ -764,11 +775,8 @@ typedef struct JsonExprState
NullableDatum
formatted_expr, /* formatted context item value */
- coercion_expr, /* input for JSON item coercion */
pathspec; /* path specification value */
- ExprState *default_on_empty; /* ON EMPTY DEFAULT expression */
- ExprState *default_on_error; /* ON ERROR DEFAULT expression */
List *args; /* passing arguments */
} JsonExprState;
@@ -831,8 +839,8 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
ExprContext *econtext, TupleTableSlot *slot);
extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
-extern void ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op,
- ExprContext *econtext);
+extern int ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op);
+extern bool ExecEvalJsonExprSkip(ExprState *state, ExprEvalStep *op);
extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup,
ExprContext *aggcontext);
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index e9134b7eab4..a33a770f7aa 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -280,10 +280,6 @@ extern Datum JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper,
bool *empty, bool *error, List *vars);
extern JsonbValue *JsonPathValue(Datum jb, JsonPath *jp, bool *empty,
bool *error, List *vars);
-
-extern int EvalJsonPathVar(void *vars, char *varName, int varNameLen,
- JsonbValue *val, JsonbValue *baseObject);
-
extern PGDLLIMPORT const TableFuncRoutine JsonbTableRoutine;
#endif
--
2.17.1
v9-0009-Remove-support-of-DEFAULT-ON-EMPTY-in-SQL-JSON-fu.patch (text/x-patch, 28.2K) download
From d7e694a3006dbca17fd156724c8b1ac9b511ad11 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Mon, 29 Aug 2022 23:23:02 +0300
Subject: [PATCH v9 9/9] Remove support of DEFAULT ON EMPTY in SQL/JSON
functions
---
doc/src/sgml/func.sgml | 4 +-
src/backend/executor/execExpr.c | 21 ------
src/backend/executor/execExprInterp.c | 27 ++++----
src/backend/jit/llvm/llvmjit_expr.c | 32 +++------
src/backend/parser/parse_expr.c | 33 ++-------
src/include/executor/execExpr.h | 3 +-
src/test/regress/expected/jsonb_sqljson.out | 74 +++++++++------------
src/test/regress/sql/jsonb_sqljson.sql | 34 +++++-----
8 files changed, 82 insertions(+), 146 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8dd63c04556..8e03a1831ab 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17976,7 +17976,7 @@ FROM
<parameter>context_item</parameter>, <parameter>path_expression</parameter>
<optional> <literal>PASSING</literal> { <parameter>value</parameter> <literal>AS</literal> <parameter>varname</parameter> } <optional>, ...</optional></optional>
<optional> <literal>RETURNING</literal> <parameter>data_type</parameter> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <parameter>expression</parameter> } <literal>ON EMPTY</literal> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> } <literal>ON EMPTY</literal> </optional>
<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <parameter>expression</parameter> } <literal>ON ERROR</literal> </optional>)
</para>
<para>
@@ -18017,7 +18017,7 @@ FROM
<optional> <literal>RETURNING</literal> <parameter>data_type</parameter> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>
<optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
<optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <parameter>expression</parameter> } <literal>ON EMPTY</literal> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } } <literal>ON EMPTY</literal> </optional>
<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <parameter>expression</parameter> } <literal>ON ERROR</literal> </optional>)
</para>
<para>
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 68227ebee21..5ea7e6b421a 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4166,9 +4166,7 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
int skip_step_off;
int execpath_step_off;
int done_step_off;
- int default_on_empty_step_off = -1;
int default_on_error_step_off = -1;
- int default_on_empty_jump_off = -1;
/* JSON_TABLE preudo-function returns context item as a result */
if (jexpr->op == JSON_TABLE_OP)
@@ -4235,16 +4233,6 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
execpath_step_off = state->steps_len;
ExprEvalPushStep(state, scratch);
- if (jexpr->on_empty && jexpr->on_empty->default_expr)
- {
- default_on_empty_step_off = state->steps_len;
- ExecInitExprRec((Expr *) jexpr->on_empty->default_expr, state, resv, resnull);
-
- scratch->opcode = EEOP_JUMP;
- default_on_empty_jump_off = state->steps_len;
- ExprEvalPushStep(state, scratch);
- }
-
if (jexpr->on_error->default_expr)
{
default_on_error_step_off = state->steps_len;
@@ -4261,15 +4249,6 @@ ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
/* EEOP_JSONEXPR_PATH */
step = &state->steps[execpath_step_off];
step->d.jsonexpr.jump_done = done_step_off;
- step->d.jsonexpr.jump_default_on_empty =
- default_on_empty_step_off > 0 ? default_on_empty_step_off : done_step_off;
step->d.jsonexpr.jump_default_on_error =
default_on_error_step_off > 0 ? default_on_error_step_off : done_step_off;
-
- /* EEOP_JUMP */
- if (default_on_empty_jump_off > 0)
- {
- step = &state->steps[default_on_empty_jump_off];
- step->d.jump.jumpdone = done_step_off;
- }
}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index d39223e0d15..9a18413c6e8 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -1855,14 +1855,10 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_CASE(EEOP_JSONEXPR_PATH)
{
/* too complex for an inline implementation */
- int res = ExecEvalJsonExpr(state, op);
-
- if (res == 0)
- EEO_JUMP(op->d.jsonexpr.jump_default_on_empty);
- else if (res == -1)
- EEO_JUMP(op->d.jsonexpr.jump_default_on_error);
- else
+ if (ExecEvalJsonExpr(state, op))
EEO_JUMP(op->d.jsonexpr.jump_done);
+ else
+ EEO_JUMP(op->d.jsonexpr.jump_default_on_error);
}
EEO_CASE(EEOP_LAST)
@@ -5400,11 +5396,10 @@ ExecEvalJsonExprSkip(ExprState *state, ExprEvalStep *op)
* ----------------------------------------------------------------
*
* Return value:
- * 1 - Ok, jump to the end of JsonExpr
- * 0 - empty result, need to execute DEFAULT ON EMPTY expression
- * -1 - error occured, need to execute DEFAULT ON ERROR expression
+ * true - Ok, jump to the end of JsonExpr
+ * false - error occured, need to execute DEFAULT ON ERROR expression
*/
-int
+bool
ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op)
{
JsonExprState *jsestate = op->d.jsonexpr.jsestate;
@@ -5436,14 +5431,16 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op)
error = true; /* fallback to error behavior */
}
- else if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT)
- return 0; /* jump to ON EMPTY expression */
else
+ {
+ Assert(jexpr->on_empty->btype != JSON_BEHAVIOR_DEFAULT); /* not supported */
+
/* Execute ON EMPTY behavior */
res = ExecEvalJsonBehavior(jexpr->on_empty,
jexpr->returning->typid,
jexpr->returning->typmod,
op->resnull);
+ }
}
if (error)
@@ -5451,7 +5448,7 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op)
Assert(!throw_errors);
if (jexpr->on_error->btype == JSON_BEHAVIOR_DEFAULT)
- return -1; /* jump to ON ERROR expression */
+ return false; /* jump to ON ERROR expression */
/* Execute ON ERROR behavior */
res = ExecEvalJsonBehavior(jexpr->on_error,
@@ -5461,5 +5458,5 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op)
}
*op->resvalue = res;
- return 1; /* jump to the end of expression */
+ return true; /* jump to the end of expression */
}
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 7681a77cd63..e0890fb63f1 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2441,34 +2441,22 @@ llvm_compile_expr(ExprState *state)
case EEOP_JSONEXPR_PATH:
{
LLVMValueRef v_ret;
- LLVMValueRef v_switch;
- int n_jumps = 0;
v_ret = build_EvalXFunc(b, mod, "ExecEvalJsonExpr",
v_state, op);
-
- if (op->d.jsonexpr.jump_default_on_empty != op->d.jsonexpr.jump_done)
- n_jumps++;
+ v_ret = LLVMBuildZExt(b, v_ret, TypeStorageBool, "");
if (op->d.jsonexpr.jump_default_on_error != op->d.jsonexpr.jump_done)
- n_jumps++;
-
- if (!n_jumps)
- LLVMBuildBr(b, opblocks[op->d.jsonexpr.jump_done]);
+ LLVMBuildCondBr(b,
+ LLVMBuildICmp(b,
+ LLVMIntEQ,
+ v_ret,
+ l_sbool_const(0),
+ ""),
+ opblocks[op->d.jsonexpr.jump_default_on_error],
+ opblocks[op->d.jsonexpr.jump_done]);
else
- {
- v_switch = LLVMBuildSwitch(b, v_ret,
- opblocks[op->d.jsonexpr.jump_done],
- n_jumps);
-
- if (op->d.jsonexpr.jump_default_on_empty != op->d.jsonexpr.jump_done)
- LLVMAddCase(v_switch, l_int32_const(0),
- opblocks[op->d.jsonexpr.jump_default_on_empty]);
-
- if (op->d.jsonexpr.jump_default_on_error != op->d.jsonexpr.jump_done)
- LLVMAddCase(v_switch, l_int32_const(-1),
- opblocks[op->d.jsonexpr.jump_default_on_error]);
- }
+ LLVMBuildBr(b, opblocks[op->d.jsonexpr.jump_done]);
break;
}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index c02530a3671..f96334ce088 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4263,7 +4263,6 @@ static Node *
coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr,
bool is_on_empty, Node *defexpr)
{
- Node *orig_defexpr = defexpr;
int location;
Oid exprtype;
@@ -4276,6 +4275,12 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr,
if (location < 0)
location = jsexpr->location;
+ if (is_on_empty)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DEFAULT ON EMPTY expressions are not supported"),
+ parser_errposition(pstate, location)));
+
defexpr = coerce_to_target_type(pstate,
defexpr,
exprtype,
@@ -4293,31 +4298,7 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr,
format_type_be(jsexpr->returning->typid)),
parser_errposition(pstate, location)));
- /* Don't allow unsafe DEFAUL ON EMPTY expressions, unless ERROR ON ERROR */
- if (!is_on_empty ||
- jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
- return defexpr;
-
- if (!expr_can_throw_errors(defexpr))
- return defexpr;
-
- /* Try to simplify expression if there is non-empty coercion */
- if (defexpr != orig_defexpr &&
- !expr_can_throw_errors(orig_defexpr))
- {
- defexpr = eval_const_expressions(NULL, defexpr);
-
- if (!expr_can_throw_errors(defexpr))
- return defexpr;
- }
-
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("unsafe DEFAULT ON EMPTY expressions are not supported"),
- errhint("Use ERROR ON ERROR clause or try to simplify expression into constant-like form."),
- parser_errposition(pstate, location)));
-
- return NULL;
+ return defexpr;
}
/*
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index b005bf440dd..d67419c66a9 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -698,7 +698,6 @@ typedef struct ExprEvalStep
{
struct JsonExprState *jsestate;
int jump_done;
- int jump_default_on_empty; /* ON EMPTY DEFAULT expression */
int jump_default_on_error; /* ON ERROR DEFAULT expression */
} jsonexpr;
@@ -839,7 +838,7 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
ExprContext *econtext, TupleTableSlot *slot);
extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
-extern int ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op);
+extern bool ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op);
extern bool ExecEvalJsonExprSkip(ExprState *state, ExprEvalStep *op);
extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup,
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index 0e07bbc858f..fcd921130f5 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -537,11 +537,9 @@ SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
(1 row)
SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
- json_value
-------------
- 2
-(1 row)
-
+ERROR: DEFAULT ON EMPTY expressions are not supported
+LINE 1: SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY ...
+ ^
SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
json_value
------------
@@ -576,7 +574,7 @@ SELECT
jsonb '{"a": 1, "b": 2}',
'$.* ? (@ > $x)' PASSING x AS x
RETURNING int
- DEFAULT -1 ON EMPTY
+ NULL ON EMPTY
DEFAULT -2 ON ERROR
) y
FROM
@@ -585,7 +583,7 @@ FROM
---+----
0 | -2
1 | 2
- 2 | -1
+ 2 |
(3 rows)
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
@@ -634,31 +632,25 @@ SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int
DEFAULT 1 / x ON EMPTY
DEFAULT 2 ON ERROR)
FROM (VALUES (1::int), (0)) x(x);
-ERROR: unsafe DEFAULT ON EMPTY expressions are not supported
+ERROR: DEFAULT ON EMPTY expressions are not supported
LINE 2: DEFAULT 1 / x ON EMPTY
^
-HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form.
SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY);
- json_value
-------------
- -1
-(1 row)
-
+ERROR: DEFAULT ON EMPTY expressions are not supported
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPT...
+ ^
SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY);
- json_value
-------------
- -1
-(1 row)
-
+ERROR: DEFAULT ON EMPTY expressions are not supported
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EM...
+ ^
SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY);
-ERROR: invalid input syntax for type integer: "err"
+ERROR: DEFAULT ON EMPTY expressions are not supported
LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON E...
^
SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY);
-ERROR: unsafe DEFAULT ON EMPTY expressions are not supported
+ERROR: DEFAULT ON EMPTY expressions are not supported
LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ...
^
-HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form.
-- JSON_QUERY
SELECT
JSON_QUERY(js, '$'),
@@ -827,7 +819,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
(1 row)
-SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY DEFAULT '"empty"' ON ERROR);
json_query
------------
"empty"
@@ -1055,7 +1047,7 @@ CREATE TABLE test_jsonb_constraints (
CONSTRAINT test_jsonb_constraint2
CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
CONSTRAINT test_jsonb_constraint3
- CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i)
+ CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int NULL ON EMPTY ERROR ON ERROR) > i)
CONSTRAINT test_jsonb_constraint4
CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
CONSTRAINT test_jsonb_constraint5
@@ -1073,7 +1065,7 @@ CREATE TABLE test_jsonb_constraints (
Check constraints:
"test_jsonb_constraint1" CHECK (js IS JSON)
"test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr))
- "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT ('12'::text || i)::integer ON EMPTY ERROR ON ERROR) > i)
+ "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer ERROR ON ERROR) > i)
"test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
"test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
"test_jsonb_constraint6" CHECK (JSON_EXISTS(js::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2)
@@ -1087,7 +1079,7 @@ ORDER BY 1;
((JSON_EXISTS((js)::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2))
((JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")))
((JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb))
- ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT (('12'::text || i))::integer ON EMPTY ERROR ON ERROR) > i))
+ ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer ERROR ON ERROR) > i))
((js IS JSON))
(JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr))
(6 rows)
@@ -1424,22 +1416,22 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR O
ERROR: jsonpath member accessor can only be applied to an object
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
ERROR: no SQL/JSON item
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt;
a
---
2
(1 row)
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt;
a
---
2
(1 row)
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt;
a
---
- 1
+
(1 row)
-- JSON_TABLE: EXISTS PATH types
@@ -1761,7 +1753,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1790,7 +1782,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1820,7 +1812,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1850,7 +1842,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1880,7 +1872,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1909,7 +1901,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1938,7 +1930,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1966,7 +1958,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -1994,7 +1986,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -2025,7 +2017,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -2195,7 +2187,7 @@ SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
123
(1 row)
-SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' ERROR ON EMPTY DEFAULT 'foo' ON ERROR);
json_value
------------
foo
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index 43a194ad09f..76d4c9cc14e 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -148,7 +148,7 @@ SELECT
jsonb '{"a": 1, "b": 2}',
'$.* ? (@ > $x)' PASSING x AS x
RETURNING int
- DEFAULT -1 ON EMPTY
+ NULL ON EMPTY
DEFAULT -2 ON ERROR
) y
FROM
@@ -237,7 +237,7 @@ SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ARRAY ON EMPTY);
SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
+SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY DEFAULT '"empty"' ON ERROR);
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
@@ -309,7 +309,7 @@ CREATE TABLE test_jsonb_constraints (
CONSTRAINT test_jsonb_constraint2
CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
CONSTRAINT test_jsonb_constraint3
- CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i)
+ CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int NULL ON EMPTY ERROR ON ERROR) > i)
CONSTRAINT test_jsonb_constraint4
CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
CONSTRAINT test_jsonb_constraint5
@@ -515,9 +515,9 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' NULL ON EMPTY DEFAULT 2 ON ERROR)) jt;
-- JSON_TABLE: EXISTS PATH types
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
@@ -776,7 +776,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -791,7 +791,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -807,7 +807,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -823,7 +823,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -839,7 +839,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -855,7 +855,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -871,7 +871,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -887,7 +887,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -903,7 +903,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -919,7 +919,7 @@ from
jtt.js,'strict $[*]' as p
columns (
n for ordinality,
- a int path 'lax $.a' default -1 on empty,
+ a int path 'lax $.a' error on empty default -1 on error,
nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
nested path 'strict $.c[*]' as pc columns ( c int path '$' )
)
@@ -987,7 +987,7 @@ FROM JSON_TABLE(
-- Extension: non-constant JSON path
SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
-SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
+SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' ERROR ON EMPTY DEFAULT 'foo' ON ERROR);
SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
-- Should fail (invalid path)
--
2.17.1
view thread (95+ messages) latest in thread
Message-ID: <c3b315b6-1e9f-6aa4-8708-daa19cf3f1a3@postgrespro.ru>
Permalink: ../c3b315b6-1e9f-6aa4-8708-daa19cf3f1a3@postgrespro.ru/
Also on: postgresql.org/message-id/c3b315b6-1e9f-6aa4-8708-daa19cf3f1a3@postgrespro.ru
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru, amitlangote09@gmail.com, andrew@dunslane.net, jkatz@postgresql.org, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, andres@anarazel.de, pgsql-hackers@lists.postgresql.org, michael@paquier.xyz, john.naylor@enterprisedb.com
Subject: Re: SQL/JSON features for v15
In-Reply-To: <c3b315b6-1e9f-6aa4-8708-daa19cf3f1a3@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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