While reviewing the patch, I found some inconsistency on json_table EXISTS.
--tested based on your patch and master.
src4=# SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb
EXISTS PATH '$'));
ERROR: cannot cast behavior expression of type boolean to jsonb
src4=# SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb
EXISTS PATH '$' error on error));
a
------
true
(1 row)
Why explicitly "error on error" not report error while not explicitly
mentioning it yields an error?
"(a jsonb EXISTS PATH '$' error on error)" returns jsonb 'true'
imply that no errors happened.
so "(a jsonb EXISTS PATH '$')" should not have any errors.
but boolean cannot cast to jsonb so for JSON_TABLE,
we should reject
COLUMNS (a jsonb EXISTS PATH '$' error on error ));
COLUMNS (a jsonb EXISTS PATH '$' unknown on error ));
at an earlier stage.
because json_populate_type will use literal 'true'/'false' cast to
jsonb, which will not fail.
but JsonPathExists returns are *not* quoted true/false.
so rejecting it earlier is better than handling it at ExecEvalJsonExprPath.
attached patch trying to solve the problem, changes applied based on
your 0001, 0002.
after apply attached patch:
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
i found out a typo in
src/test/regress/expected/sqljson_queryfuncs.out,
src/test/regress/sql/sqljson_queryfuncs.sql
"fixed-legth" should be "fixed-length"
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
