v1-0001-better-handle-json_table-EXISTS-ERROR-UNKNOWN-ON-.patch

From bf05974a330a1df5877e77a0484922b4d17ccde9 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 23 Jul 2024 19:57:26 +0800
Subject: [PATCH v1 1/1] better handle json_table EXISTS ERROR|UNKNOWN ON ERROR
 cases

bool cannot cast to jsonb/json.
so the following two expression should fail.
(column_name jsonb EXISTS PATH path_expression error on error);
(column_name jsonb EXISTS PATH path_expression unknown on error);

make it fail at parsing stage.
---
 src/backend/parser/parse_expr.c                 |  9 +++++++++
 src/test/regress/expected/sqljson_jsontable.out | 15 +++++++++++++--
 src/test/regress/sql/sqljson_jsontable.sql      | 10 ++++++++++
 3 files changed, 32 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 61611b8a..92dbf854 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4262,6 +4262,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 	Node	   *path_spec;
 	const char *func_name = NULL;
 	JsonFormatType default_format;
+	Oid returning_typid;
 
 	switch (func->op)
 	{
@@ -4478,6 +4479,14 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			if (jsexpr->returning->typid != BOOLOID)
 				jsexpr->use_json_coercion = jsexpr->returning->typid != INT4OID;
 
+			returning_typid = getBaseType(jsexpr->returning->typid);
+
+			if (returning_typid == JSONBOID || returning_typid == JSONOID)
+					ereport(ERROR,
+							errcode(ERRCODE_CANNOT_COERCE),
+							errmsg("cannot cast type boolean to %s",
+							format_type_be(jsexpr->returning->typid)));
+
 			jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
 													 JSON_BEHAVIOR_FALSE,
 													 jsexpr->returning);
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 9e93307f..b8779276 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -560,6 +560,17 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR
  1
 (1 row)
 
+--corner cases:
+--(column_name, jsonb EXISTS PATH '$' unknown on error)
+--(column_name, jsonb EXISTS PATH '$' error on error)
+create domain djsonb as jsonb check(value = 'true');
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a djsonb EXISTS PATH '$' error on error));
+ERROR:  cannot cast type boolean to djsonb
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a djsonb EXISTS PATH '$' unknown on error));
+ERROR:  cannot cast type boolean to djsonb
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$'));
+ERROR:  cannot cast type boolean to jsonb
+drop domain djsonb;
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
 ERROR:  cannot cast behavior expression of type boolean to smallint
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
@@ -582,9 +593,9 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' E
 (1 row)
 
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
-ERROR:  cannot cast behavior expression of type boolean to json
+ERROR:  cannot cast type boolean to json
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-ERROR:  cannot cast behavior expression of type boolean to jsonb
+ERROR:  cannot cast type boolean to jsonb
 -- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
   item   
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index f6fc09b7..09fd5c66 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -264,6 +264,16 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT
 -- JSON_TABLE: EXISTS PATH types
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a' ERROR ON ERROR));	-- ok; can cast to int4
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR ON ERROR));	-- ok; can cast to int4
+
+--corner cases:
+--(column_name, jsonb EXISTS PATH '$' unknown on error)
+--(column_name, jsonb EXISTS PATH '$' error on error)
+create domain djsonb as jsonb check(value = 'true');
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a djsonb EXISTS PATH '$' error on error));
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a djsonb EXISTS PATH '$' unknown on error));
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$'));
+drop domain djsonb;
+
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-- 
2.34.1