v1-0002-refactor-QUOTE-behavior-for-json_query.no-cfbot
application/octet-stream
Filename: v1-0002-refactor-QUOTE-behavior-for-json_query.no-cfbot
Type: application/octet-stream
Part: 1
Message:
Re: remaining sql/json patches
From 2081e447ae516cfd05ec4df7ed5e23773decd260 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 3 Jan 2024 18:26:13 +0800
Subject: [PATCH v2 2/2] refactor QUOTE behavior for json_query
We need to know the QUOTE behavior in the function ExecEvalJsonCoercion.
because for ExecEvalJsonCoercion, the coercion datum source can be scalar string items.
scalar string items in jsonb have the double quotes surrounding it, you may
keep it or omit it. to make sure ExecEvalJsonCoercion can distinguish between
keep or omit quotes, I added a bool keep_quotes to struct JsonCoercion. After
refactoring, in function ExecEvalJsonCoercion, keep_quotes call
JsonbToCString, omit_quotes call JsonbUnquote. also we can only choose
coercion->via_io | coercion->via_populate, I add two assertions to enforce
it.
---
src/backend/executor/execExprInterp.c | 43 +++++++++++-----
src/backend/parser/parse_expr.c | 18 +++++--
src/include/nodes/primnodes.h | 3 +-
src/test/regress/expected/jsonb_sqljson.out | 55 ++++++++++++++++++++-
src/test/regress/sql/jsonb_sqljson.sql | 15 ++++++
5 files changed, 115 insertions(+), 19 deletions(-)
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 699664fb..5d075043 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4523,8 +4523,37 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
ErrorSaveContext *escontext = op->d.jsonexpr_coercion.escontext;
Datum res = *op->resvalue;
bool resnull = *op->resnull;
+ Jsonb *jb = NULL;
- if (coercion->via_populate)
+ /* choose one, either via_io or via_populate */
+ Assert(!(coercion->via_io && coercion->via_populate));
+ Assert(!(!coercion->via_io && !coercion->via_populate));
+
+ if (resnull)
+ {
+ *op->resvalue = (Datum) 0;
+ return;
+ }
+ jb = DatumGetJsonbP(res);
+
+ /* scalar item should coerce via_io */
+ if (coercion->via_io || (JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb)))
+ {
+ FmgrInfo *input_finfo = op->d.jsonexpr_coercion.input_finfo;
+ Oid typioparam = op->d.jsonexpr_coercion.typioparam;
+ char *val_string;
+
+ if (coercion->keep_quotes)
+ val_string = JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+ else
+ val_string = JsonbUnquote(jb);
+
+ (void) InputFunctionCallSafe(input_finfo, val_string, typioparam,
+ coercion->targettypmod,
+ (Node *) escontext,
+ op->resvalue);
+ }
+ else if (coercion->via_populate)
{
void *cache = op->d.jsonexpr_coercion.json_populate_type_cache;
@@ -4535,18 +4564,6 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
econtext->ecxt_per_query_memory,
op->resnull, (Node *) escontext);
}
- else if (coercion->via_io)
- {
- FmgrInfo *input_finfo = op->d.jsonexpr_coercion.input_finfo;
- Oid typioparam = op->d.jsonexpr_coercion.typioparam;
- char *val_string = resnull ? NULL :
- JsonbUnquote(DatumGetJsonbP(res));
-
- (void) InputFunctionCallSafe(input_finfo, val_string, typioparam,
- coercion->targettypmod,
- (Node *) escontext,
- op->resvalue);
- }
}
/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index e474e064..6027b34e 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4321,6 +4321,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
break;
case JSON_QUERY_OP:
+ /* by default, json_query keep quotes. */
jsexpr->wrapper = func->wrapper;
jsexpr->omit_quotes = (func->quotes == JS_QUOTES_OMIT);
@@ -4333,6 +4334,12 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
}
jsexpr->result_coercion = coerceJsonFuncExprOutput(pstate, jsexpr);
+ if (jsexpr->result_coercion)
+ {
+ JsonCoercion *coercion = (JsonCoercion *) jsexpr->result_coercion;
+ if (!jsexpr->omit_quotes)
+ coercion->keep_quotes = true;
+ }
if (func->on_empty)
jsexpr->on_empty = transformJsonBehavior(pstate,
func->on_empty,
@@ -4420,10 +4427,6 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func,
jsexpr->format = func->context_item->format;
- /* Both set in the caller. */
- jsexpr->result_coercion = NULL;
- jsexpr->omit_quotes = false;
-
pathspec = transformExprRecurse(pstate, func->pathspec);
jsexpr->path_spec =
@@ -4513,6 +4516,13 @@ coerceJsonFuncExprOutput(ParseState *pstate, JsonExpr *jsexpr)
coercion->via_populate =
coercion->via_populate || jsexpr->wrapper != JSW_NONE;
+ /*
+ * after makeJsonCoercion, there are some cases where
+ * both via_io and via_populate will be true.
+ * we can only choose one. we choose via_populate.
+ */
+ if (coercion->via_populate)
+ coercion->via_io = false;
return (Node *) coercion;
}
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 192c141b..037af91c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1744,6 +1744,7 @@ typedef struct JsonCoercion
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 */
+ bool keep_quotes; /* keep quotes or not. see ExecEvalJsonCoercion */
} JsonCoercion;
/*
@@ -1852,7 +1853,7 @@ typedef struct JsonExpr
/* WRAPPER specification for JSON_QUERY */
JsonWrapper wrapper;
- /* KEEP or OMIT QUOTES for singleton scalars returned by JSON_QUERY() */
+ /* KEEP or OMIT QUOTES for singleton scalars returned by JSON_QUERY() or JSON_VALUE() */
bool omit_quotes;
/* Original JsonFuncExpr's location */
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index ef52e4ce..e0613cc2 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -643,6 +643,37 @@ SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
[1]
(1 row)
+-- test QUOTES behavior.
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] omit quotes);
+ json_query
+------------
+ {1,2,3}
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes error on error);
+ERROR: malformed array literal: ""{1,2,3}""
+DETAIL: Array value must start with "{" or dimension information.
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range omit quotes);
+ json_query
+------------
+ [1,3)
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes error on error);
+ERROR: malformed range literal: ""[1,2]""
+DETAIL: Missing left parenthesis or bracket.
SELECT JSON_QUERY(jsonb '[]', '$[*]');
json_query
------------
@@ -832,6 +863,24 @@ FROM
4 | 4 | [4]
(25 rows)
+-- record type returning with quotes behavior.
+CREATE TYPE comp_abc AS (a text, b int, c timestamp);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc omit quotes);
+ json_query
+-------------------------------------
+ (abc,42,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes);
+ json_query
+------------
+
+(1 row)
+
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes error on error);
+ERROR: malformed record literal: ""(abc,42,01.02.2003)""
+DETAIL: Missing left parenthesis.
+DROP TYPE comp_abc;
-- Extension: record types returning
CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
@@ -902,7 +951,11 @@ SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
(1 row)
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
-ERROR: domain sqljsonb_int_not_null does not allow null values
+ json_query
+------------
+
+(1 row)
+
-- Test timestamptz passing and output
SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
json_query
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index 81d45e93..bc028398 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -193,6 +193,14 @@ SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
+-- test QUOTES behavior.
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] omit quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes error on error);
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range omit quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes error on error);
+
SELECT JSON_QUERY(jsonb '[]', '$[*]');
SELECT JSON_QUERY(jsonb '[]', '$[*]' NULL ON EMPTY);
SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
@@ -244,6 +252,13 @@ FROM
generate_series(0, 4) x,
generate_series(0, 4) y;
+-- record type returning with quotes behavior.
+CREATE TYPE comp_abc AS (a text, b int, c timestamp);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc omit quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes);
+SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning comp_abc keep quotes error on error);
+DROP TYPE comp_abc;
+
-- Extension: record types returning
CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
--
2.34.1