agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
3+ messages / 2 participants
[nested] [flat]
* BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
@ 2026-08-17 14:04 PG Bug reporting form <noreply@postgresql.org>
2026-08-17 14:09 ` Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false' =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-08-17 14:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: zengman@halodbtech.com
The following bug has been logged on the website:
Bug reference: 19625
Logged by: Man Zeng
Email address: zengman@halodbtech.com
PostgreSQL version: 19beta3
Operating system: 24.04.1-Ubuntu
Description:
Hi all,
A boolean DEFAULT expression in the SQL/JSON functions can be silently
replaced with 'false', discarding the user's expression at parse time.
For example:
```sql
postgres=# SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT true ON
ERROR); -- expected true
json_value
------------
false
(1 row)
postgres=# SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT (1=1) ON
ERROR); -- expected true
json_value
------------
false
(1 row)
postgres=# SELECT JSON_QUERY('{"a":"abc"}', 'strict $.b' DEFAULT false ON
ERROR);
json_query
------------
false
(1 row)
```
The user asked for true, so the result is wrong. A boolean
expression such as DEFAULT (1=1) is affected in the same way, and so
is JSON_QUERY(). Non-boolean DEFAULT expressions (e.g. DEFAULT 'ok')
return the correct value, and so do boolean ones when the RETURNING
type is boolean or integer.
The problem is in transformJsonBehavior() in parse_expr.c. When the
DEFAULT expression is boolean-valued and the RETURNING type is neither
boolean nor integer (or a domain over integer), the code guesses the
jsonb value from the behavior type:
```c
char *val = btype == JSON_BEHAVIOR_TRUE ? "true" : "false";
```
This only works for the canned TRUE/FALSE constants. A user-supplied
DEFAULT clause has btype == JSON_BEHAVIOR_DEFAULT, so the condition is
always false and the expression is replaced with jsonb 'false' without
being evaluated.
I think we can use to_jsonb() to handle all of these cases -- whether a
canned TRUE/FALSE constant or a user-supplied boolean DEFAULT
expression, the value is converted per the expression's actual
evaluation (there is no bool->jsonb cast, hence the explicit function
call). Below is my change:
```c
diff --git a/src/backend/parser/parse_expr.c
b/src/backend/parser/parse_expr.c
index 30c889f505f..175b3d3ee69 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4956,20 +4956,17 @@ transformJsonBehavior(ParseState *pstate, JsonExpr
*jsexpr,
coerce_at_runtime = true;
/*
- * json_populate_type() expects to be passed a jsonb
value, so gin
- * up a Const containing the appropriate boolean
value represented
- * as jsonb, discarding the original Const
containing a plain
- * boolean.
+ * json_populate_type() only takes a jsonb value, so
convert a
+ * boolean to jsonb by calling to_jsonb() on it.
That way any
+ * boolean-valued expression -- whether a canned
TRUE/FALSE
+ * constant or a user-supplied DEFAULT expression --
is converted
+ * according to the value it actually evaluates to.
*/
if (exprType(expr) == BOOLOID)
- {
- char *val = btype ==
JSON_BEHAVIOR_TRUE ? "true" : "false";
-
- expr = (Node *) makeConst(JSONBOID, -1,
InvalidOid, -1,
-
DirectFunctionCall1(jsonb_in,
-
CStringGetDatum(val)),
-
false, false);
- }
+ expr = (Node *) makeFuncExpr(F_TO_JSONB,
JSONBOID,
+
list_make1(expr),
+
InvalidOid, InvalidOid,
+
COERCE_EXPLICIT_CALL);
}
else
{
```
Thoughts?
--
Regards,
Man Zeng
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
2026-08-17 14:04 BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false' PG Bug reporting form <noreply@postgresql.org>
@ 2026-08-17 14:09 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
2026-08-24 03:37 ` Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false' =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
0 siblings, 1 reply; 3+ messages in thread
From: zengman @ 2026-08-17 14:09 UTC (permalink / raw)
To: 曾满 <zengman@halodbtech.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Hi,
The formatting above seems a bit messed up, so I am re-attaching the diff file.
--
Regards,
Man Zeng
Attachments:
[application/octet-stream] json-bool-default-fix.patch (1.3K, ../../tencent_0AAF798D129E099858B151E9@qq.com/2-json-bool-default-fix.patch)
download | inline diff:
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 30c889f505f..175b3d3ee69 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4956,20 +4956,17 @@ transformJsonBehavior(ParseState *pstate, JsonExpr *jsexpr,
coerce_at_runtime = true;
/*
- * json_populate_type() expects to be passed a jsonb value, so gin
- * up a Const containing the appropriate boolean value represented
- * as jsonb, discarding the original Const containing a plain
- * boolean.
+ * json_populate_type() only takes a jsonb value, so convert a
+ * boolean to jsonb by calling to_jsonb() on it. That way any
+ * boolean-valued expression -- whether a canned TRUE/FALSE
+ * constant or a user-supplied DEFAULT expression -- is converted
+ * according to the value it actually evaluates to.
*/
if (exprType(expr) == BOOLOID)
- {
- char *val = btype == JSON_BEHAVIOR_TRUE ? "true" : "false";
-
- expr = (Node *) makeConst(JSONBOID, -1, InvalidOid, -1,
- DirectFunctionCall1(jsonb_in,
- CStringGetDatum(val)),
- false, false);
- }
+ expr = (Node *) makeFuncExpr(F_TO_JSONB, JSONBOID,
+ list_make1(expr),
+ InvalidOid, InvalidOid,
+ COERCE_EXPLICIT_CALL);
}
else
{
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
2026-08-17 14:04 BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false' PG Bug reporting form <noreply@postgresql.org>
2026-08-17 14:09 ` Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false' =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
@ 2026-08-24 03:37 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
0 siblings, 0 replies; 3+ messages in thread
From: zengman @ 2026-08-24 03:37 UTC (permalink / raw)
To: 曾满 <zengman@halodbtech.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Added regression tests and rebased.
--
Regards,
Man Zeng
Attachments:
[application/octet-stream] 0002-json-bool-default-fix.patch (3.1K, ../../tencent_46CEA91A192F54AA1F1DC64B@qq.com/2-0002-json-bool-default-fix.patch)
download | inline diff:
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 30c889f505f..175b3d3ee69 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4956,20 +4956,17 @@ transformJsonBehavior(ParseState *pstate, JsonExpr *jsexpr,
coerce_at_runtime = true;
/*
- * json_populate_type() expects to be passed a jsonb value, so gin
- * up a Const containing the appropriate boolean value represented
- * as jsonb, discarding the original Const containing a plain
- * boolean.
+ * json_populate_type() only takes a jsonb value, so convert a
+ * boolean to jsonb by calling to_jsonb() on it. That way any
+ * boolean-valued expression -- whether a canned TRUE/FALSE
+ * constant or a user-supplied DEFAULT expression -- is converted
+ * according to the value it actually evaluates to.
*/
if (exprType(expr) == BOOLOID)
- {
- char *val = btype == JSON_BEHAVIOR_TRUE ? "true" : "false";
-
- expr = (Node *) makeConst(JSONBOID, -1, InvalidOid, -1,
- DirectFunctionCall1(jsonb_in,
- CStringGetDatum(val)),
- false, false);
- }
+ expr = (Node *) makeFuncExpr(F_TO_JSONB, JSONBOID,
+ list_make1(expr),
+ InvalidOid, InvalidOid,
+ COERCE_EXPLICIT_CALL);
}
else
{
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index ff64dce0c59..50d9d230420 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -554,6 +554,25 @@ select json_value('{"a": 1.234}', '$.a' returning int error on error);
ERROR: invalid input syntax for type integer: "1.234"
select json_value('{"a": "1.234"}', '$.a' returning int error on error);
ERROR: invalid input syntax for type integer: "1.234"
+-- Test JSON_VALUE DEFAULT ON ERROR boolean expressions
+SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT false ON ERROR);
+ json_value
+------------
+ false
+(1 row)
+
+SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT true ON ERROR);
+ json_value
+------------
+ true
+(1 row)
+
+SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT (1=1) ON ERROR);
+ json_value
+------------
+ true
+(1 row)
+
-- JSON_QUERY
SELECT JSON_VALUE(NULL::jsonb, '$');
json_value
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a69ef253f66..d9b6f2bcca2 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -149,6 +149,11 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
select json_value('{"a": 1.234}', '$.a' returning int error on error);
select json_value('{"a": "1.234"}', '$.a' returning int error on error);
+-- Test JSON_VALUE DEFAULT ON ERROR boolean expressions
+SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT false ON ERROR);
+SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT true ON ERROR);
+SELECT JSON_VALUE('{"a":"abc"}', 'strict $.b' DEFAULT (1=1) ON ERROR);
+
-- JSON_QUERY
SELECT JSON_VALUE(NULL::jsonb, '$');
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-08-24 03:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-17 14:04 BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false' PG Bug reporting form <noreply@postgresql.org>
2026-08-17 14:09 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
2026-08-24 03:37 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox