Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7f56eaf

Browse files
committed
SQL/JSON: Fix casting for integer EXISTS columns in JSON_TABLE
The current method of coercing the boolean result value ofJsonPathExists() 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 theinteger input function doesn't recognize boolean literal values asvalid.Instead use the boolean-to-integer cast function for coercion in thatcase so that using integer or domains thereof as type for EXISTScolumns works. Note that coercion for ON ERROR values TRUE and FALSEalready works like that because the parser creates a cast expressionincluding the cast function, but the coercion of the actual resultvalue 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.comBackpatch-through: 17
1 parent74c9669 commit7f56eaf

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
staticvoidExecInitJsonCoercion(ExprState*state,JsonReturning*returning,
9595
ErrorSaveContext*escontext,boolomit_quotes,
96+
boolexists_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
elseif (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
staticvoid
45034508
ExecInitJsonCoercion(ExprState*state,JsonReturning*returning,
45044509
ErrorSaveContext*escontext,boolomit_quotes,
4510+
boolexists_coerce,
45054511
Datum*resv,bool*resnull)
45064512
{
45074513
ExprEvalStepscratch= {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
Oidtargettype;
709709
int32targettypmod;
710710
boolomit_quotes;
711-
void*json_populate_type_cache;
711+
/* exists_* fields only relevant for JSON_EXISTS_OP. */
712+
boolexists_coerce;
713+
boolexists_cast_to_int;
714+
boolexists_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 (aintPATH'lax $.a' DEFAULT1ON EMPTY DEFAULT2ON ERROR)) jt;
263263

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

277+
-- EXISTS PATH domain over int
278+
CREATEDOMAINdint4ASint;
279+
CREATEDOMAINdint4_0ASintCHECK (VALUE<>0 );
280+
SELECT a, a::boolFROM JSON_TABLE(jsonb'"a"','$' COLUMNS (a dint4 EXISTSPATH'$.a' ));
281+
SELECT a, a::boolFROM JSON_TABLE(jsonb'{"a":1}','$' COLUMNS (a dint4_0 EXISTSPATH'$.b'));
282+
SELECT a, a::boolFROM JSON_TABLE(jsonb'{"a":1}','$' COLUMNS (a dint4_0 EXISTSPATH'$.b' ERRORON ERROR));
283+
SELECT a, a::boolFROM JSON_TABLE(jsonb'{"a":1}','$' COLUMNS (a dint4_0 EXISTSPATH'$.b' FALSEON ERROR));
284+
SELECT a, a::boolFROM JSON_TABLE(jsonb'{"a":1}','$' COLUMNS (a dint4_0 EXISTSPATH'$.b' TRUEON ERROR));
285+
DROPDOMAIN dint4, dint4_0;
286+
276287
-- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
277288
SELECT*FROM JSON_TABLE(jsonb'"world"','$' COLUMNS (itemtextPATH'$' KEEP QUOTESON SCALAR STRING));
278289
SELECT*FROM JSON_TABLE(jsonb'"world"','$' COLUMNS (itemtextPATH'$' OMIT QUOTESON SCALAR STRING));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp