Skip to content

Commit f6a2529

Browse files
amitlanamitlan
committed
Fix JsonExpr deparsing to emit QUOTES and WRAPPER correctly
Currently, get_json_expr_options() does not emit the default values for QUOTES (KEEP QUOTES) and WRAPPER (WITHOUT WRAPPER). That causes the deparsed JSON_TABLE() columns, such as those contained in a a view's query, to behave differently when executed than the original definition. That's because the rules encoded in transformJsonTableColumns() will choose either JSON_VALUE() or JSON_QUERY() as implementation to execute a given column's path expression depending on the QUOTES and WRAPPER specificationd and they 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 parent 561b74d commit f6a2529

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
else if (jsexpr->wrapper == JSW_UNCONDITIONAL)
88508850
appendStringInfo(context->buf, " WITH UNCONDITIONAL WRAPPER");
8851+
/* The default */
8852+
else if (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 WRAPPER OMIT 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 WRAPPER OMIT 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 WRAPPER OMIT 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 WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
1084+
"test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) WITHOUT WRAPPER OMIT 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 WRAPPER OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
1093+
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY 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)