aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/executor/execExpr.c19
-rw-r--r--src/backend/executor/execExprInterp.c46
-rw-r--r--src/include/executor/execExpr.h6
-rw-r--r--src/test/regress/expected/sqljson_jsontable.out34
-rw-r--r--src/test/regress/sql/sqljson_jsontable.sql13
5 files changed, 102 insertions, 16 deletions
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1caf48036b..9b52bab52fc 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -93,6 +93,7 @@ static void ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
ExprEvalStep *scratch);
static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
ErrorSaveContext *escontext, bool omit_quotes,
+ bool exists_coerce,
Datum *resv, bool *resnull);
@@ -4329,7 +4330,9 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
jsestate->jump_eval_coercion = state->steps_len;
ExecInitJsonCoercion(state, jsexpr->returning, escontext,
- jsexpr->omit_quotes, resv, resnull);
+ jsexpr->omit_quotes,
+ jsexpr->op == JSON_EXISTS_OP,
+ resv, resnull);
}
else if (jsexpr->use_io_coercion)
{
@@ -4410,7 +4413,8 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
/* Step to coerce the ON ERROR expression if needed */
if (jsexpr->on_error->coerce)
ExecInitJsonCoercion(state, jsexpr->returning, escontext,
- jsexpr->omit_quotes, resv, resnull);
+ jsexpr->omit_quotes, false,
+ resv, resnull);
/*
* Add a COERCION_FINISH step to check for errors that may occur when
@@ -4466,7 +4470,8 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
/* Step to coerce the ON EMPTY expression if needed */
if (jsexpr->on_empty->coerce)
ExecInitJsonCoercion(state, jsexpr->returning, escontext,
- jsexpr->omit_quotes, resv, resnull);
+ jsexpr->omit_quotes, false,
+ resv, resnull);
/*
* Add a COERCION_FINISH step to check for errors that may occur when
@@ -4502,6 +4507,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
static void
ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
ErrorSaveContext *escontext, bool omit_quotes,
+ bool exists_coerce,
Datum *resv, bool *resnull)
{
ExprEvalStep scratch = {0};
@@ -4512,8 +4518,13 @@ ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
scratch.resnull = resnull;
scratch.d.jsonexpr_coercion.targettype = returning->typid;
scratch.d.jsonexpr_coercion.targettypmod = returning->typmod;
- scratch.d.jsonexpr_coercion.json_populate_type_cache = NULL;
+ scratch.d.jsonexpr_coercion.json_coercion_cache = NULL;
scratch.d.jsonexpr_coercion.escontext = escontext;
scratch.d.jsonexpr_coercion.omit_quotes = omit_quotes;
+ scratch.d.jsonexpr_coercion.exists_coerce = exists_coerce;
+ scratch.d.jsonexpr_coercion.exists_cast_to_int = exists_coerce &&
+ getBaseType(returning->typid) == INT4OID;
+ scratch.d.jsonexpr_coercion.exists_check_domain = exists_coerce &&
+ DomainHasConstraints(returning->typid);
ExprEvalPushStep(state, &scratch);
}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 430438f668e..1633ea83731 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4303,13 +4303,7 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
if (!error)
{
*op->resnull = false;
- if (jsexpr->use_json_coercion)
- *op->resvalue = DirectFunctionCall1(jsonb_in,
- BoolGetDatum(exists) ?
- CStringGetDatum("true") :
- CStringGetDatum("false"));
- else
- *op->resvalue = BoolGetDatum(exists);
+ *op->resvalue = BoolGetDatum(exists);
}
}
break;
@@ -4550,10 +4544,46 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
{
ErrorSaveContext *escontext = op->d.jsonexpr_coercion.escontext;
+ /*
+ * Prepare to call json_populate_type() to coerce the boolean result of
+ * JSON_EXISTS_OP to the target type. If the the target type is integer
+ * or a domain over integer, call the boolean-to-integer cast function
+ * instead, because the integer's input function (which is what
+ * json_populate_type() calls to coerce to scalar target types) doesn't
+ * accept boolean literals as valid input. We only have a special case
+ * for integer and domains thereof as it seems common to use those types
+ * for EXISTS columns in JSON_TABLE().
+ */
+ if (op->d.jsonexpr_coercion.exists_coerce)
+ {
+ if (op->d.jsonexpr_coercion.exists_cast_to_int)
+ {
+ /* Check domain constraints if any. */
+ if (op->d.jsonexpr_coercion.exists_check_domain &&
+ !domain_check_safe(*op->resvalue, *op->resnull,
+ op->d.jsonexpr_coercion.targettype,
+ &op->d.jsonexpr_coercion.json_coercion_cache,
+ econtext->ecxt_per_query_memory,
+ (Node *) escontext))
+ {
+ *op->resnull = true;
+ *op->resvalue = (Datum) 0;
+ }
+ else
+ *op->resvalue = DirectFunctionCall1(bool_int4, *op->resvalue);
+ return;
+ }
+
+ *op->resvalue = DirectFunctionCall1(jsonb_in,
+ DatumGetBool(*op->resvalue) ?
+ CStringGetDatum("true") :
+ CStringGetDatum("false"));
+ }
+
*op->resvalue = json_populate_type(*op->resvalue, JSONBOID,
op->d.jsonexpr_coercion.targettype,
op->d.jsonexpr_coercion.targettypmod,
- &op->d.jsonexpr_coercion.json_populate_type_cache,
+ &op->d.jsonexpr_coercion.json_coercion_cache,
econtext->ecxt_per_query_memory,
op->resnull,
op->d.jsonexpr_coercion.omit_quotes,
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 55337d49166..d983a9a7fed 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -708,7 +708,11 @@ typedef struct ExprEvalStep
Oid targettype;
int32 targettypmod;
bool omit_quotes;
- void *json_populate_type_cache;
+ /* exists_* fields only relevant for JSON_EXISTS_OP. */
+ bool exists_coerce;
+ bool exists_cast_to_int;
+ bool exists_check_domain;
+ void *json_coercion_cache;
ErrorSaveContext *escontext;
} jsonexpr_coercion;
} d;
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 24892d5e18a..9a97dd90d78 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -175,7 +175,7 @@ FROM json_table_test vals
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | f | 0 | | false
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | f | 0 | | false
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | f | 0 | | false
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | t | 0 | | true
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | t | 1 | 1 | true
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | f | 0 | | false
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | f | 0 | | false
(14 rows)
@@ -549,12 +549,18 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT
(1 row)
-- JSON_TABLE: EXISTS PATH types
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a' ERROR ON ERROR)); -- ok; can cast to int4
a
---
0
(1 row)
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR ON ERROR)); -- ok; can cast to int4
+ a
+---
+ 1
+(1 row)
+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
DETAIL: invalid input syntax for type smallint: "false"
@@ -588,6 +594,30 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
false
(1 row)
+-- EXISTS PATH domain over int
+CREATE DOMAIN dint4 AS int;
+CREATE DOMAIN dint4_0 AS int CHECK (VALUE <> 0 );
+SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4 EXISTS PATH '$.a' ));
+ a | a
+---+---
+ 0 | f
+(1 row)
+
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b'));
+ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
+DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check"
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' ERROR ON ERROR));
+ERROR: value for domain dint4_0 violates check constraint "dint4_0_check"
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' FALSE ON ERROR));
+ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
+DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check"
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' TRUE ON ERROR));
+ a | a
+---+---
+ 1 | t
+(1 row)
+
+DROP DOMAIN dint4, dint4_0;
-- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
item
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index dc509bed8af..38992316f5a 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -262,7 +262,8 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAU
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
-- JSON_TABLE: EXISTS PATH types
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a' ERROR ON ERROR)); -- ok; can cast to int4
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR ON ERROR)); -- ok; can cast to int4
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
@@ -273,6 +274,16 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' E
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
+-- EXISTS PATH domain over int
+CREATE DOMAIN dint4 AS int;
+CREATE DOMAIN dint4_0 AS int CHECK (VALUE <> 0 );
+SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4 EXISTS PATH '$.a' ));
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b'));
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' ERROR ON ERROR));
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' FALSE ON ERROR));
+SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' TRUE ON ERROR));
+DROP DOMAIN dint4, dint4_0;
+
-- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' OMIT QUOTES ON SCALAR STRING));
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-28 16:04:39 +0000