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

Commitf6a2529

Browse files
committed
Fix JsonExpr deparsing to emit QUOTES and WRAPPER correctly
Currently, get_json_expr_options() does not emit the default valuesfor QUOTES (KEEP QUOTES) and WRAPPER (WITHOUT WRAPPER). That causesthe deparsed JSON_TABLE() columns, such as those contained in a aview's query, to behave differently when executed than the originaldefinition. That's because the rules encoded intransformJsonTableColumns() will choose either JSON_VALUE() orJSON_QUERY() as implementation to execute a given column's pathexpression depending on the QUOTES and WRAPPER specificationd andthey have slightly different semantics.Reported-by: Jian He <jian.universality@gmail.com>Discussion:https://postgr.es/m/CACJufxEqhqsfrg_p7EMyo5zak3d767iFDL8vz_4%3DZBHpOtrghw%40mail.gmail.com
1 parent561b74d commitf6a2529

File tree

3 files changed

+41
-35
lines changed

3 files changed

+41
-35
lines changed

‎src/backend/utils/adt/ruleutils.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8848,9 +8848,15 @@ get_json_expr_options(JsonExpr *jsexpr, deparse_context *context,
88488848
appendStringInfo(context->buf," WITH CONDITIONAL WRAPPER");
88498849
elseif (jsexpr->wrapper==JSW_UNCONDITIONAL)
88508850
appendStringInfo(context->buf," WITH UNCONDITIONAL WRAPPER");
8851+
/* The default */
8852+
elseif (jsexpr->wrapper==JSW_NONE||jsexpr->wrapper==JSW_UNSPEC)
8853+
appendStringInfo(context->buf," WITHOUT WRAPPER");
88518854

88528855
if (jsexpr->omit_quotes)
88538856
appendStringInfo(context->buf," OMIT QUOTES");
8857+
/* The default */
8858+
else
8859+
appendStringInfo(context->buf," KEEP QUOTES");
88548860
}
88558861

88568862
if (jsexpr->on_empty&&jsexpr->on_empty->btype!=default_behavior)

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

Lines changed: 22 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -302,11 +302,11 @@ CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
302302
1 + 2 AS a,
303303
'"foo"'::json AS "b c"
304304
COLUMNS (
305-
js json PATH '$',
306-
jb jsonb PATH '$',
307-
jst text FORMAT JSON PATH '$',
308-
jsc character(4) FORMAT JSON PATH '$',
309-
jsv character varying(4) FORMAT JSON PATH '$'
305+
js json PATH '$' WITHOUT WRAPPER KEEP QUOTES,
306+
jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES,
307+
jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES,
308+
jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES,
309+
jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES
310310
)
311311
)
312312
\sv jsonb_table_view4
@@ -321,8 +321,8 @@ CREATE OR REPLACE VIEW public.jsonb_table_view4 AS
321321
1 + 2 AS a,
322322
'"foo"'::json AS "b c"
323323
COLUMNS (
324-
jsb jsonb PATH '$',
325-
jsbq jsonb PATH '$' OMIT QUOTES,
324+
jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES,
325+
jsbq jsonb PATH '$'WITHOUT WRAPPEROMIT QUOTES,
326326
aaa integer PATH '$."aaa"',
327327
aaa1 integer PATH '$."aaa"'
328328
)
@@ -357,12 +357,12 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
357357
1 + 2 AS a,
358358
'"foo"'::json AS "b c"
359359
COLUMNS (
360-
js2 json PATH '$',
361-
jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER,
362-
jsb2q jsonb PATH '$' OMIT QUOTES,
363-
ia integer[] PATH '$',
364-
ta text[] PATH '$',
365-
jba jsonb[] PATH '$'
360+
js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES,
361+
jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES,
362+
jsb2q jsonb PATH '$'WITHOUT WRAPPEROMIT QUOTES,
363+
ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES,
364+
ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES,
365+
jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES
366366
)
367367
)
368368
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
@@ -374,19 +374,19 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
374374
(3 rows)
375375

376376
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
377-
QUERY PLAN
378-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
377+
QUERY PLAN
378+
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
379379
Table Function Scan on "json_table"
380380
Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv
381-
Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$'))
381+
Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES))
382382
(3 rows)
383383

384384
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
385-
QUERY PLAN
386-
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
385+
QUERY PLAN
386+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
387387
Table Function Scan on "json_table"
388388
Output: "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1
389-
Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"'))
389+
Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"'))
390390
(3 rows)
391391

392392
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
@@ -398,11 +398,11 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
398398
(3 rows)
399399

400400
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6;
401-
QUERY PLAN
402-
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
401+
QUERY PLAN
402+
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
403403
Table Function Scan on "json_table"
404404
Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba
405-
Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$'))
405+
Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$'WITHOUT WRAPPEROMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES))
406406
(3 rows)
407407

408408
-- JSON_TABLE() with alias

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

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1070,27 +1070,27 @@ CREATE TABLE test_jsonb_constraints (
10701070
CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C")
10711071
);
10721072
\d test_jsonb_constraints
1073-
Table "public.test_jsonb_constraints"
1074-
Column | Type | Collation | Nullable | Default
1075-
--------+---------+-----------+----------+--------------------------------------------------------------------------------
1073+
Table "public.test_jsonb_constraints"
1074+
Column | Type | Collation | Nullable |Default
1075+
--------+---------+-----------+----------+--------------------------------------------------------------------------------------------
10761076
js | text | | |
10771077
i | integer | | |
1078-
x | jsonb | | | JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER)
1078+
x | jsonb | | | JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER KEEP QUOTES)
10791079
Check constraints:
10801080
"test_jsonb_constraint1" CHECK (js IS JSON)
10811081
"test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr))
10821082
"test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
1083-
"test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
1084-
"test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
1083+
"test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPERKEEP QUOTESEMPTY OBJECT ON ERROR) < '[10]'::jsonb)
1084+
"test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5)WITHOUT WRAPPEROMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
10851085

10861086
SELECT check_clause
10871087
FROM information_schema.check_constraints
10881088
WHERE constraint_name LIKE 'test_jsonb_constraint%'
10891089
ORDER BY 1;
1090-
check_clause
1091-
------------------------------------------------------------------------------------------------------------------------
1092-
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
1093-
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
1090+
check_clause
1091+
----------------------------------------------------------------------------------------------------------------------------------------
1092+
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5)WITHOUT WRAPPEROMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
1093+
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPERKEEP QUOTESEMPTY OBJECT ON ERROR) < '[10]'::jsonb)
10941094
(JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
10951095
(js IS JSON)
10961096
JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr)
@@ -1100,9 +1100,9 @@ SELECT pg_get_expr(adbin, adrelid)
11001100
FROM pg_attrdef
11011101
WHERE adrelid = 'test_jsonb_constraints'::regclass
11021102
ORDER BY 1;
1103-
pg_get_expr
1104-
--------------------------------------------------------------------------------
1105-
JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER)
1103+
pg_get_expr
1104+
--------------------------------------------------------------------------------------------
1105+
JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER KEEP QUOTES)
11061106
(1 row)
11071107

11081108
INSERT INTO test_jsonb_constraints VALUES ('', 1);

0 commit comments

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