hi. https://wiki.postgresql.org/wiki/PostgreSQL_17_Open_Items#Open_Issues issue: Problems with deparsed SQL/JSON query function
original the bug report link: https://postgr.es/m/cacjufxeqhqsfrg_p7emyo5zak3d767ifdl8vz_4%3dzbhpotr...@mail.gmail.com forgive me for putting it in the new email thread. I made the following change, added several tests on it. --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4636,10 +4636,10 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior, { expr = transformExprRecurse(pstate, behavior->expr); if (!IsA(expr, Const) && !IsA(expr, FuncExpr) && - !IsA(expr, OpExpr)) + !IsA(expr, OpExpr) && !IsA(expr, CoerceViaIO) && !IsA(expr, CoerceToDomain)) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("can only specify a constant, non-aggregate function, or operator expression for DEFAULT"), + errmsg("can only specify a constant, non-aggregate function, or operator expression or cast expression for DEFAULT"), parser_errposition(pstate, exprLocation(expr)))); if (contain_var_clause(expr)) ereport(ERROR, these two expression node also looks like Const: CoerceViaIO: "foo1"'::jsonb::text CoerceToDomain: 'foo'::jsonb_test_domain we need to deal with these two, otherwise we cannot use domain type in DEFAULT expression. also the following should not fail: SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' returning text DEFAULT '"foo1"'::text::json::text ON ERROR); we have `if (contain_var_clause(expr))` further check it, so it should be fine?
From 124cd4245266343daecdb4294b2013d9ebdd6b24 Mon Sep 17 00:00:00 2001 From: jian he <jian.universality@gmail.com> Date: Mon, 15 Apr 2024 12:37:36 +0800 Subject: [PATCH v1 1/1] in transformJsonBehavior better handle default expression sql/json query functions for process json can return empty or error. We can specify DEFAULT expression for handling empty or error cases while applying the path expression to the json or while type coercion. the default expression can be just a plain constant, however, a constant can be formed as a cast expression,eg (1::jsonb::text). so allow the DEFAULT expression formed as CoerceViaIO node or CoerceToDomain node in transformJsonBehavior for better handling these cases. Discussion: https://postgr.es/m/CACJufxEqhqsfrg_p7EMyo5zak3d767iFDL8vz_4%3DZBHpOtrghw@mail.gmail.com --- src/backend/parser/parse_expr.c | 4 +-- .../regress/expected/sqljson_jsontable.out | 25 ++++++++++++++++++ .../regress/expected/sqljson_queryfuncs.out | 26 +++++++++++++++++-- src/test/regress/sql/sqljson_jsontable.sql | 13 ++++++++++ src/test/regress/sql/sqljson_queryfuncs.sql | 7 +++++ 5 files changed, 71 insertions(+), 4 deletions(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 4c98d7a0..94dbb531 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4636,10 +4636,10 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior, { expr = transformExprRecurse(pstate, behavior->expr); if (!IsA(expr, Const) && !IsA(expr, FuncExpr) && - !IsA(expr, OpExpr)) + !IsA(expr, OpExpr) && !IsA(expr, CoerceViaIO) && !IsA(expr, CoerceToDomain)) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("can only specify a constant, non-aggregate function, or operator expression for DEFAULT"), + errmsg("can only specify a constant, non-aggregate function, or operator expression or cast expression for DEFAULT"), parser_errposition(pstate, exprLocation(expr)))); if (contain_var_clause(expr)) ereport(ERROR, diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index a00eec8a..b45dd32a 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -113,6 +113,31 @@ FROM json_table_test vals [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | "str" | "str | | | "str" | "\"str\"" | "\"str\"" (14 rows) +--check default expression +SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR)); + js1 +-------- + "foo1" +(1 row) + +SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR)); +ERROR: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check" +SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR)); + js1 +------ + foo1 +(1 row) + +SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR)); + js1 +------ + foo2 +(1 row) + -- "formatted" columns SELECT * FROM json_table_test vals diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out index 9e86b0da..375b712a 100644 --- a/src/test/regress/expected/sqljson_queryfuncs.out +++ b/src/test/regress/expected/sqljson_queryfuncs.out @@ -1213,15 +1213,37 @@ ERROR: DEFAULT expression must not contain column references LINE 1: SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT b + 1 ON ER... ^ SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT sum(1) over() ON ERROR) FROM test_jsonb_mutability; -ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT +ERROR: can only specify a constant, non-aggregate function, or operator expression or cast expression for DEFAULT LINE 1: SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT sum(1) over... ^ SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT (SELECT 1) ON ERROR) FROM test_jsonb_mutability; -ERROR: can only specify a constant, non-aggregate function, or operator expression for DEFAULT +ERROR: can only specify a constant, non-aggregate function, or operator expression or cast expression for DEFAULT LINE 1: SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT (SELECT 1) ... ^ DROP TABLE test_jsonb_mutability; DROP FUNCTION ret_setint; +CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); +SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo'::jsonb_test_domain ON ERROR); +ERROR: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check" +SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR); + json_value +------------ + foo1 +(1 row) + +SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR); + json_value +------------ + "foo1" +(1 row) + +SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR); + json_value +------------ + foo1 +(1 row) + +drop domain jsonb_test_domain; -- Extension: non-constant JSON path SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a'); json_exists diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 3752ccc4..3c00734f 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -63,6 +63,19 @@ FROM json_table_test vals ) jt ON true; +--check default expression +SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT '"foo1"'::jsonb::text ON ERROR)); + +SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo'::jsonb_test_domain ON ERROR)); + +SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON ERROR)); + +SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$' + COLUMNS (js1 jsonb_test_domain path '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR)); + -- "formatted" columns SELECT * FROM json_table_test vals diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql index ec330d3b..a8cde83d 100644 --- a/src/test/regress/sql/sqljson_queryfuncs.sql +++ b/src/test/regress/sql/sqljson_queryfuncs.sql @@ -411,6 +411,13 @@ SELECT JSON_QUERY(js, '$' RETURNING int DEFAULT (SELECT 1) ON ERROR) FROM test_ DROP TABLE test_jsonb_mutability; DROP FUNCTION ret_setint; +CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); +SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo'::jsonb_test_domain ON ERROR); +SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR); +SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' returning jsonb_test_domain DEFAULT '"foo1"'::jsonb::text ON ERROR); +SELECT JSON_VALUE(jsonb '{"d1": "foo"}', '$.a2' returning jsonb_test_domain DEFAULT 'foo1'::jsonb_test_domain ON ERROR); +drop domain jsonb_test_domain; + -- Extension: non-constant JSON path SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a'); SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a'); base-commit: d21d61b96f7a4d89e4b2e7cc9b9a1ec3f642fa12 -- 2.34.1
