Incorrect matching of sql/json PASSING variable names

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