Skip to content

Commit 7f56eaf

Browse files
amitlanamitlan
committed
SQL/JSON: Fix casting for integer EXISTS columns in JSON_TABLE
The current method of coercing the boolean result value of JsonPathExists() to the target type specified for an EXISTS column, which is to call the type's input function via json_populate_type(), leads to an error when the target type is integer, because the integer input function doesn't recognize boolean literal values as valid. Instead use the boolean-to-integer cast function for coercion in that case so that using integer or domains thereof as type for EXISTS columns works. Note that coercion for ON ERROR values TRUE and FALSE already works like that because the parser creates a cast expression including the cast function, but the coercion of the actual result value is not handled by the parser. Tests by Jian He. Reported-by: Jian He <jian.universality@gmail.com> Author: Jian He <jian.universality@gmail.com> Author: Amit Langote <amitlangote09@gmail.com> Discussion: https://postgr.es/m/CACJufxEo4sUjKCYtda0_qt9tazqqKPmF1cqhW9KBOUeJFqQd2g@mail.gmail.com Backpatch-through: 17
1 parent 74c9669 commit 7f56eaf

File tree

5 files changed

+102
-16
lines changed

5 files changed

+102
-16
lines changed

‎src/backend/executor/execExpr.c

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,7 @@ static void ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
9393
ExprEvalStep *scratch);
9494
static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
9595
ErrorSaveContext *escontext, bool omit_quotes,
96+
bool exists_coerce,
9697
Datum *resv, bool *resnull);
9798

9899

@@ -4329,7 +4330,9 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
43294330
jsestate->jump_eval_coercion = state->steps_len;
43304331

43314332
ExecInitJsonCoercion(state, jsexpr->returning, escontext,
4332-
jsexpr->omit_quotes, resv, resnull);
4333+
jsexpr->omit_quotes,
4334+
jsexpr->op == JSON_EXISTS_OP,
4335+
resv, resnull);
43334336
}
43344337
else if (jsexpr->use_io_coercion)
43354338
{
@@ -4410,7 +4413,8 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
44104413
/* Step to coerce the ON ERROR expression if needed */
44114414
if (jsexpr->on_error->coerce)
44124415
ExecInitJsonCoercion(state, jsexpr->returning, escontext,
4413-
jsexpr->omit_quotes, resv, resnull);
4416+
jsexpr->omit_quotes, false,
4417+
resv, resnull);
44144418

44154419
/*
44164420
* Add a COERCION_FINISH step to check for errors that may occur when
@@ -4466,7 +4470,8 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
44664470
/* Step to coerce the ON EMPTY expression if needed */
44674471
if (jsexpr->on_empty->coerce)
44684472
ExecInitJsonCoercion(state, jsexpr->returning, escontext,
4469-
jsexpr->omit_quotes, resv, resnull);
4473+
jsexpr->omit_quotes, false,
4474+
resv, resnull);
44704475

44714476
/*
44724477
* Add a COERCION_FINISH step to check for errors that may occur when
@@ -4502,6 +4507,7 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
45024507
static void
45034508
ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
45044509
ErrorSaveContext *escontext, bool omit_quotes,
4510+
bool exists_coerce,
45054511
Datum *resv, bool *resnull)
45064512
{
45074513
ExprEvalStep scratch = {0};
@@ -4512,8 +4518,13 @@ ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
45124518
scratch.resnull = resnull;
45134519
scratch.d.jsonexpr_coercion.targettype = returning->typid;
45144520
scratch.d.jsonexpr_coercion.targettypmod = returning->typmod;
4515-
scratch.d.jsonexpr_coercion.json_populate_type_cache = NULL;
4521+
scratch.d.jsonexpr_coercion.json_coercion_cache = NULL;
45164522
scratch.d.jsonexpr_coercion.escontext = escontext;
45174523
scratch.d.jsonexpr_coercion.omit_quotes = omit_quotes;
4524+
scratch.d.jsonexpr_coercion.exists_coerce = exists_coerce;
4525+
scratch.d.jsonexpr_coercion.exists_cast_to_int = exists_coerce &&
4526+
getBaseType(returning->typid) == INT4OID;
4527+
scratch.d.jsonexpr_coercion.exists_check_domain = exists_coerce &&
4528+
DomainHasConstraints(returning->typid);
45184529
ExprEvalPushStep(state, &scratch);
45194530
}

‎src/backend/executor/execExprInterp.c

Lines changed: 38 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -4303,13 +4303,7 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
43034303
if (!error)
43044304
{
43054305
*op->resnull = false;
4306-
if (jsexpr->use_json_coercion)
4307-
*op->resvalue = DirectFunctionCall1(jsonb_in,
4308-
BoolGetDatum(exists) ?
4309-
CStringGetDatum("true") :
4310-
CStringGetDatum("false"));
4311-
else
4312-
*op->resvalue = BoolGetDatum(exists);
4306+
*op->resvalue = BoolGetDatum(exists);
43134307
}
43144308
}
43154309
break;
@@ -4550,10 +4544,46 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
45504544
{
45514545
ErrorSaveContext *escontext = op->d.jsonexpr_coercion.escontext;
45524546

4547+
/*
4548+
* Prepare to call json_populate_type() to coerce the boolean result of
4549+
* JSON_EXISTS_OP to the target type. If the the target type is integer
4550+
* or a domain over integer, call the boolean-to-integer cast function
4551+
* instead, because the integer's input function (which is what
4552+
* json_populate_type() calls to coerce to scalar target types) doesn't
4553+
* accept boolean literals as valid input. We only have a special case
4554+
* for integer and domains thereof as it seems common to use those types
4555+
* for EXISTS columns in JSON_TABLE().
4556+
*/
4557+
if (op->d.jsonexpr_coercion.exists_coerce)
4558+
{
4559+
if (op->d.jsonexpr_coercion.exists_cast_to_int)
4560+
{
4561+
/* Check domain constraints if any. */
4562+
if (op->d.jsonexpr_coercion.exists_check_domain &&
4563+
!domain_check_safe(*op->resvalue, *op->resnull,
4564+
op->d.jsonexpr_coercion.targettype,
4565+
&op->d.jsonexpr_coercion.json_coercion_cache,
4566+
econtext->ecxt_per_query_memory,
4567+
(Node *) escontext))
4568+
{
4569+
*op->resnull = true;
4570+
*op->resvalue = (Datum) 0;
4571+
}
4572+
else
4573+
*op->resvalue = DirectFunctionCall1(bool_int4, *op->resvalue);
4574+
return;
4575+
}
4576+
4577+
*op->resvalue = DirectFunctionCall1(jsonb_in,
4578+
DatumGetBool(*op->resvalue) ?
4579+
CStringGetDatum("true") :
4580+
CStringGetDatum("false"));
4581+
}
4582+
45534583
*op->resvalue = json_populate_type(*op->resvalue, JSONBOID,
45544584
op->d.jsonexpr_coercion.targettype,
45554585
op->d.jsonexpr_coercion.targettypmod,
4556-
&op->d.jsonexpr_coercion.json_populate_type_cache,
4586+
&op->d.jsonexpr_coercion.json_coercion_cache,
45574587
econtext->ecxt_per_query_memory,
45584588
op->resnull,
45594589
op->d.jsonexpr_coercion.omit_quotes,

‎src/include/executor/execExpr.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -708,7 +708,11 @@ typedef struct ExprEvalStep
708708
Oid targettype;
709709
int32 targettypmod;
710710
bool omit_quotes;
711-
void *json_populate_type_cache;
711+
/* exists_* fields only relevant for JSON_EXISTS_OP. */
712+
bool exists_coerce;
713+
bool exists_cast_to_int;
714+
bool exists_check_domain;
715+
void *json_coercion_cache;
712716
ErrorSaveContext *escontext;
713717
} jsonexpr_coercion;
714718
} d;

‎src/test/regress/expected/sqljson_jsontable.out

Lines changed: 32 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -175,7 +175,7 @@ FROM json_table_test vals
175175
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | f | 0 | | false
176176
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | f | 0 | | false
177177
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | f | 0 | | false
178-
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | t | 0 | | true
178+
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | t | 1 | 1 | true
179179
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | f | 0 | | false
180180
[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | f | 0 | | false
181181
(14 rows)
@@ -549,12 +549,18 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT
549549
(1 row)
550550

551551
-- JSON_TABLE: EXISTS PATH types
552-
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
552+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a' ERROR ON ERROR)); -- ok; can cast to int4
553553
a
554554
---
555555
0
556556
(1 row)
557557

558+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR ON ERROR)); -- ok; can cast to int4
559+
a
560+
---
561+
1
562+
(1 row)
563+
558564
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
559565
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
560566
DETAIL: invalid input syntax for type smallint: "false"
@@ -588,6 +594,30 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
588594
false
589595
(1 row)
590596

597+
-- EXISTS PATH domain over int
598+
CREATE DOMAIN dint4 AS int;
599+
CREATE DOMAIN dint4_0 AS int CHECK (VALUE <> 0 );
600+
SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4 EXISTS PATH '$.a' ));
601+
a | a
602+
---+---
603+
0 | f
604+
(1 row)
605+
606+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b'));
607+
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
608+
DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check"
609+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' ERROR ON ERROR));
610+
ERROR: value for domain dint4_0 violates check constraint "dint4_0_check"
611+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' FALSE ON ERROR));
612+
ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
613+
DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check"
614+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' TRUE ON ERROR));
615+
a | a
616+
---+---
617+
1 | t
618+
(1 row)
619+
620+
DROP DOMAIN dint4, dint4_0;
591621
-- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
592622
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
593623
item

‎src/test/regress/sql/sqljson_jsontable.sql

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -262,7 +262,8 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAU
262262
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
263263

264264
-- JSON_TABLE: EXISTS PATH types
265-
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
265+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a' ERROR ON ERROR)); -- ok; can cast to int4
266+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR ON ERROR)); -- ok; can cast to int4
266267
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
267268
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
268269
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
273274
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
274275
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
275276

277+
-- EXISTS PATH domain over int
278+
CREATE DOMAIN dint4 AS int;
279+
CREATE DOMAIN dint4_0 AS int CHECK (VALUE <> 0 );
280+
SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4 EXISTS PATH '$.a' ));
281+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b'));
282+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' ERROR ON ERROR));
283+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' FALSE ON ERROR));
284+
SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' TRUE ON ERROR));
285+
DROP DOMAIN dint4, dint4_0;
286+
276287
-- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
277288
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
278289
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' OMIT QUOTES ON SCALAR STRING));

0 commit comments

Comments
 (0)