pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
7+ messages / 5 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>
0 siblings, 1 reply; 7+ 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
inline patch 1: src/backend/parser/parse_expr.c (+0/-0 lines) download inline-1.patch
--- 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] 7+ messages in thread
* Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
@ 2026-08-17 14:09 zengman <zengman@halodbtech.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ 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:
json-bool-default-fix.patch (application/octet-stream, 1.3K) download
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] 7+ messages in thread
* Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
@ 2026-08-24 03:37 zengman <zengman@halodbtech.com>
parent: zengman <zengman@halodbtech.com>
0 siblings, 1 reply; 7+ 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:
0002-json-bool-default-fix.patch (application/octet-stream, 3.1K) download
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] 7+ messages in thread
* Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
@ 2026-10-12 03:33 QQ Mail <781408517@qq.com>
parent: zengman <zengman@halodbtech.com>
0 siblings, 1 reply; 7+ messages in thread
From: QQ Mail @ 2026-10-12 03:33 UTC (permalink / raw)
To: zengman@halodbtech.com; +Cc: pgsql-bugs@lists.postgresql.org
Hi Man,
Thank you for the updated patch. This review was prepared with AI assistance, including the local build and test execution.
Your 0002-json-bool-default-fix.patch applied without modification to PostgreSQL commit 9e7d152488f197e96f171f0714459c227c3f7693.
An independent 40-case test matrix went from 13 passing before the patch to all 40 passing afterward. Coverage included NULL boolean defaults; JSON_VALUE/JSON_QUERY with ON ERROR and ON EMPTY; domains and constraints; varchar length enforcement; JSON_TABLE; and volatile defaults evaluated only when needed, including repeated prepared-statement execution.
All nine JSON-related regression tests passed. Testing used Linux, GCC 14.2, --enable-cassert, and CFLAGS='-O0 -g0'. This was not a full PostgreSQL regression run or cross-platform validation.
No blocking issue was found within that scope. Could the regression tests also cover NULL::boolean, JSON_QUERY with ON EMPTY, and a nonconstant boolean default? These exercise distinct failures beyond the three added JSON_VALUE constant tests.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
@ 2026-11-12 01:23 Amit Langote <amitlangote09@gmail.com>
parent: QQ Mail <781408517@qq.com>
0 siblings, 1 reply; 7+ messages in thread
From: Amit Langote @ 2026-11-12 01:23 UTC (permalink / raw)
To: QQ Mail <781408517@qq.com>; +Cc: zengman@halodbtech.com; pgsql-bugs@lists.postgresql.org
Hi,
Thanks for the report and the patch.
Attached is v2, which I intend to commit and back-patch to v17. The
main changes from the patch posted on Aug 24 are:
* I kept the existing jsonb Const for JSON_BEHAVIOR_TRUE and
JSON_BEHAVIOR_FALSE, so only a boolean-valued DEFAULT expression gets
wrapped in a to_jsonb() FuncExpr. That way the node tree for TRUE /
FALSE ON ERROR is unchanged in the back branches.
* More tests, including the cases asked for in the review posted on
Oct 12 (NULL::boolean, JSON_QUERY with ON EMPTY, and a non-constant
default).
* Added commit message noting that views and rules created before the
fix still contain the wrong 'false'::jsonb
Bug #19822 reports the same problem and the cases shown there are
fixed by this patch as well, so the commit message mentions that bug #
too.
--
Thanks, Amit Langote
Attachments:
v2-0001-SQL-JSON-Fix-boolean-DEFAULT-expressions-in-ON-ER.patch (application/octet-stream, 10.0K) download
From df2e5540f5becec4e511c0e40968c52f0b94241f Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Thu, 12 Nov 2026 09:52:58 +0900
Subject: [PATCH v2] SQL/JSON: Fix boolean DEFAULT expressions in ON ERROR / ON
EMPTY
When a boolean-valued behavior expression must be coerced to the
RETURNING type at run time using json_populate_type(),
transformJsonBehavior() replaces it with a jsonb Const, because
json_populate_type() only accepts jsonb input. The value of that
Const was chosen by looking at the behavior type, 'true' for TRUE ON
ERROR and 'false' otherwise. That is fine for the TRUE and FALSE
behaviors, but a user-supplied DEFAULT expression has behavior type
DEFAULT, so it was unconditionally replaced by 'false' without ever
looking at the expression. For example:
SELECT JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT true ON ERROR);
json_value
------------
false
The same happened for DEFAULT NULL::bool and for non-constant
expressions such as DEFAULT (1 = 1), with either ON ERROR or ON EMPTY,
in JSON_VALUE(), JSON_QUERY() and JSON_TABLE() columns. Only cases
where the RETURNING type is boolean, integer, or a domain over integer
were unaffected, as they don't take this path.
Fix by wrapping a boolean DEFAULT expression in a call to to_jsonb(),
there being no cast from boolean to jsonb, so that the value passed
to json_populate_type() is whatever the expression evaluates to. The
call is visible when the expression is deparsed, for example, a view
shows DEFAULT to_jsonb(true) ON ERROR, which reads back in to give the
same result. The TRUE and FALSE behaviors continue to use a jsonb
Const as before.
Note that this does not fix views, rules, or other stored expressions
created before this fix, since those already contain the incorrect
'false'::jsonb constant. They will need to be recreated to get the
correct result. We'll need to mention this in the release notes.
Bug: #19625
Bug: #19822
Reported-by: Man Zeng <zengman@halodbtech.com>
Reported-by: Chunling Qin <303677365@qq.com>
Author: Man Zeng <zengman@halodbtech.com>
Co-authored-by: Amit Langote <amitlangote09@gmail.com>
Discussion: https://postgr.es/m/19625-683b498c92087bc8@postgresql.org
Discussion: https://postgr.es/m/19822-8bdbbe6b7cdc9048@postgresql.org
Backpatch-through: 17
---
src/backend/parser/parse_expr.c | 38 ++++++++---
.../regress/expected/sqljson_queryfuncs.out | 68 +++++++++++++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 29 ++++++++
3 files changed, 125 insertions(+), 10 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 728392f436f..575ce3a9a42 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4931,19 +4931,37 @@ 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() expects to be passed a jsonb value, so a
+ * boolean expression must be converted to jsonb first.
+ *
+ * For a user-supplied DEFAULT expression, do that by wrapping it
+ * in a call to to_jsonb(), there being no cast from boolean to
+ * jsonb, so that the result reflects whatever the expression
+ * evaluates to, including NULL.
+ *
+ * Otherwise, the expression is the boolean Const made by
+ * GetJsonBehaviorConst() for TRUE or FALSE, which is simply
+ * replaced by a jsonb Const containing the same value.
*/
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);
+ if (btype == JSON_BEHAVIOR_DEFAULT)
+ expr = (Node *) makeFuncExpr(F_TO_JSONB, JSONBOID,
+ list_make1(expr),
+ InvalidOid, InvalidOid,
+ COERCE_EXPLICIT_CALL);
+ else
+ {
+ char *val;
+
+ Assert(btype == JSON_BEHAVIOR_TRUE ||
+ btype == JSON_BEHAVIOR_FALSE);
+ val = (btype == JSON_BEHAVIOR_TRUE) ? "true" : "false";
+ expr = (Node *) makeConst(JSONBOID, -1, InvalidOid, -1,
+ DirectFunctionCall1(jsonb_in,
+ CStringGetDatum(val)),
+ false, false);
+ }
}
}
else
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index c0086de4c7e..f5c696c0da8 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1776,3 +1776,71 @@ SELECT JSON_VALUE(x, '$' RETURNING jsonb) FROM (VALUES ('1'::jsonb), (NULL), ('2
2
(3 rows)
+-- Check that a boolean DEFAULT expression that must be coerced to the
+-- RETURNING type at run time yields the value it evaluates to, rather than
+-- always false (bug #19625).
+SELECT JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT true ON ERROR) AS t,
+ JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT false ON ERROR) AS f,
+ JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT NULL::bool ON ERROR) IS NULL AS n;
+ t | f | n
+------+-------+---
+ true | false | t
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' DEFAULT (1 < 2) ON EMPTY) AS t,
+ JSON_VALUE(jsonb '{"a": 1}', '$.b' DEFAULT (random() < 0) ON EMPTY) AS f;
+ t | f
+------+-------
+ true | false
+(1 row)
+
+SELECT JSON_QUERY(jsonb '{"a": 1}', 'strict $.b' DEFAULT true ON ERROR) AS t,
+ JSON_QUERY(jsonb '{"a": 1}', '$.b' DEFAULT (1 < 2) ON EMPTY) AS t,
+ JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING text DEFAULT false ON EMPTY) AS f;
+ t | t | f
+------+------+-------
+ true | true | false
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING char(5) DEFAULT true ON EMPTY) AS t;
+ t
+-------
+ true
+(1 row)
+
+CREATE DOMAIN queryfuncs_d_bool AS bool CHECK (VALUE);
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING queryfuncs_d_bool DEFAULT true ON EMPTY);
+ json_value
+------------
+ t
+(1 row)
+
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING queryfuncs_d_bool DEFAULT false ON EMPTY);
+ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type
+DETAIL: value for domain queryfuncs_d_bool violates check constraint "queryfuncs_d_bool_check"
+DROP DOMAIN queryfuncs_d_bool;
+SELECT * FROM JSON_TABLE(jsonb '{"a": 1}', '$'
+ COLUMNS (t text PATH 'strict $.b' DEFAULT true ON ERROR,
+ f text PATH '$.b' DEFAULT (1 > 2) ON EMPTY,
+ et text EXISTS PATH 'strict $.b' TRUE ON ERROR,
+ ef text EXISTS PATH 'strict $.b' FALSE ON ERROR));
+ t | f | et | ef
+------+-------+------+-------
+ true | false | true | false
+(1 row)
+
+-- the to_jsonb() call added for the coercion is visible when deparsed
+CREATE VIEW json_bool_default_view AS
+ SELECT JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT true ON ERROR) AS t,
+ JSON_QUERY(jsonb '{"a": 1}', '$.b' DEFAULT (1 > 2) ON EMPTY) AS f;
+\sv json_bool_default_view
+CREATE OR REPLACE VIEW public.json_bool_default_view AS
+ SELECT JSON_VALUE('{"a": 1}'::jsonb, 'strict $."b"' RETURNING text DEFAULT to_jsonb(true) ON ERROR) AS t,
+ JSON_QUERY('{"a": 1}'::jsonb, '$."b"' RETURNING jsonb WITHOUT WRAPPER KEEP QUOTES DEFAULT to_jsonb(1 > 2) ON EMPTY) AS f
+SELECT * FROM json_bool_default_view;
+ t | f
+------+-------
+ true | false
+(1 row)
+
+DROP VIEW json_bool_default_view;
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index d5ea5b3d8d2..406adf7a332 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -600,3 +600,32 @@ SELECT JSON_VALUE(x, '$' RETURNING json) FROM (VALUES ('1'::json), ('null'), ('2
SELECT JSON_VALUE(x, '$.a' RETURNING jsonb) FROM (VALUES ('{"a": 1}'::jsonb), ('{}'), ('{"a": 2}')) v(x);
SELECT JSON_VALUE(x, 'strict $.a' RETURNING jsonb) FROM (VALUES ('{"a": 1}'::jsonb), ('[]'), ('{"a": 2}')) v(x);
SELECT JSON_VALUE(x, '$' RETURNING jsonb) FROM (VALUES ('1'::jsonb), (NULL), ('2')) v(x);
+
+-- Check that a boolean DEFAULT expression that must be coerced to the
+-- RETURNING type at run time yields the value it evaluates to, rather than
+-- always false (bug #19625).
+SELECT JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT true ON ERROR) AS t,
+ JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT false ON ERROR) AS f,
+ JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT NULL::bool ON ERROR) IS NULL AS n;
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' DEFAULT (1 < 2) ON EMPTY) AS t,
+ JSON_VALUE(jsonb '{"a": 1}', '$.b' DEFAULT (random() < 0) ON EMPTY) AS f;
+SELECT JSON_QUERY(jsonb '{"a": 1}', 'strict $.b' DEFAULT true ON ERROR) AS t,
+ JSON_QUERY(jsonb '{"a": 1}', '$.b' DEFAULT (1 < 2) ON EMPTY) AS t,
+ JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING text DEFAULT false ON EMPTY) AS f;
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING char(5) DEFAULT true ON EMPTY) AS t;
+CREATE DOMAIN queryfuncs_d_bool AS bool CHECK (VALUE);
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING queryfuncs_d_bool DEFAULT true ON EMPTY);
+SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING queryfuncs_d_bool DEFAULT false ON EMPTY);
+DROP DOMAIN queryfuncs_d_bool;
+SELECT * FROM JSON_TABLE(jsonb '{"a": 1}', '$'
+ COLUMNS (t text PATH 'strict $.b' DEFAULT true ON ERROR,
+ f text PATH '$.b' DEFAULT (1 > 2) ON EMPTY,
+ et text EXISTS PATH 'strict $.b' TRUE ON ERROR,
+ ef text EXISTS PATH 'strict $.b' FALSE ON ERROR));
+-- the to_jsonb() call added for the coercion is visible when deparsed
+CREATE VIEW json_bool_default_view AS
+ SELECT JSON_VALUE(jsonb '{"a": 1}', 'strict $.b' DEFAULT true ON ERROR) AS t,
+ JSON_QUERY(jsonb '{"a": 1}', '$.b' DEFAULT (1 > 2) ON EMPTY) AS f;
+\sv json_bool_default_view
+SELECT * FROM json_bool_default_view;
+DROP VIEW json_bool_default_view;
--
2.47.3
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
@ 2026-11-12 14:50 zengman <zengman@halodbtech.com>
parent: Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: zengman @ 2026-11-12 14:50 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; QQ Mail <781408517@qq.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
> Thanks for the report and the patch.
>
> Attached is v2, which I intend to commit and back-patch to v17. The
> main changes from the patch posted on Aug 24 are:
>
> * I kept the existing jsonb Const for JSON_BEHAVIOR_TRUE and
> JSON_BEHAVIOR_FALSE, so only a boolean-valued DEFAULT expression gets
> wrapped in a to_jsonb() FuncExpr. That way the node tree for TRUE /
> FALSE ON ERROR is unchanged in the back branches.
>
> * More tests, including the cases asked for in the review posted on
> Oct 12 (NULL::boolean, JSON_QUERY with ON EMPTY, and a non-constant
> default).
>
> * Added commit message noting that views and rules created before the
> fix still contain the wrong 'false'::jsonb
>
> Bug #19822 reports the same problem and the cases shown there are
> fixed by this patch as well, so the commit message mentions that bug #
> too.
Hi Amit,
Thanks for reviewing my original patch and helping improve the fix. This looks much better than my previous version!
However, while testing it, I noticed a couple of issues in the following scenarios:
```sql
CREATE TABLE jidx(j jsonb);
CREATE INDEX ON jidx
((JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY)));
CREATE TABLE jgen(
j jsonb,
x text GENERATED ALWAYS AS
(JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY)) STORED
);
```
```sql
postgres=# CREATE TABLE jidx(j jsonb);
CREATE TABLE
postgres=# CREATE INDEX ON jidx
((JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY)));
2026-11-12 22:26:27.129 CST [2061352] ERROR: functions in index expression must be marked IMMUTABLE at character 25
2026-11-12 22:26:27.129 CST [2061352] STATEMENT: CREATE INDEX ON jidx
((JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY)));
ERROR: functions in index expression must be marked IMMUTABLE
LINE 2: ((JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY)));
^
postgres=#
postgres=#
postgres=# CREATE TABLE jgen(
j jsonb,
x text GENERATED ALWAYS AS
(JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY)) STORED
);
2026-11-12 22:26:33.169 CST [2061352] ERROR: generation expression is not immutable
2026-11-12 22:26:33.169 CST [2061352] STATEMENT: CREATE TABLE jgen(
j jsonb,
x text GENERATED ALWAYS AS
(JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY)) STORED
);
ERROR: generation expression is not immutable
```
I haven't come up with a good way to address these cases yet. Do you have any thoughts on this?
Thanks again!
--
Regards,
Man Zeng
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
@ 2026-11-15 03:54 Joao Rito <joaovictor.rito@gmail.com>
parent: zengman <zengman@halodbtech.com>
0 siblings, 0 replies; 7+ messages in thread
From: Joao Rito @ 2026-11-15 03:54 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Man Zeng <zengman@halodbtech.com>; pgsql-bugs@lists.postgresql.org
Hi Amit, Man,
I tested v2 on current master (81b2179b492, built with cassert and tap tests) and it applies cleanly. On master, a boolean DEFAULT ON ERROR or ON EMPTY returned 'false' in every case I ran where the result was not already boolean: DEFAULT true, NULL::bool (the result was not NULL), (1 = 1) and (random() < 2, ON ERROR only), in JSON_VALUE, JSON_QUERY and JSON_TABLE. I used RETURNING text, char(5), varchar(10), json and jsonb. RETURNING bool was already correct on master. With v2 all of them return the expected value, and a view shows DEFAULT to_jsonb(true) and gives the same result. DEFAULT 'abc', 42, a numeric, a date and a jsonb object, and JSON_EXISTS with TRUE/FALSE/UNKNOWN ON ERROR, give the same results as on master. The new test in sqljson_queryfuncs fails on master without the C change (it shows false | false | f where true | false | t is expected), and the regress suite passes with the patch (243 tests).
I could also reproduce what Man reported after v2. On master, CREATE INDEX on JSON_VALUE(j, '$.x' DEFAULT true ON EMPTY) and a STORED generated column with the same expression both work. With v2 they fail with "functions in index expression must be marked IMMUTABLE" and "generation expression is not immutable", because to_jsonb(anyelement) is STABLE. With a non-boolean DEFAULT, the index (text, and RETURNING int) and the generated column (text) still work with v2.
What I did not test: the isolation and tap suites, pgindent, the docs, the back branches (v17 onward), other platforms (I ran one Linux build), and a fix for the immutability issue.
Small suggestion: since the index and generated column cases work today, maybe the fix could avoid a STABLE function for a constant boolean DEFAULT (for example by building the jsonb Const at parse time, as the TRUE and FALSE behaviors do), and the test could cover an index or generated column. I do not have a tested patch for that. I have no objection to the rest of the approach.
Regards,
Joao Rito
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-11-15 03:54 UTC | newest]
Thread overview: 7+ 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 ` zengman <zengman@halodbtech.com>
2026-08-24 03:37 ` zengman <zengman@halodbtech.com>
2026-10-12 03:33 ` QQ Mail <781408517@qq.com>
2026-11-12 01:23 ` Amit Langote <amitlangote09@gmail.com>
2026-11-12 14:50 ` zengman <zengman@halodbtech.com>
2026-11-15 03:54 ` Joao Rito <joaovictor.rito@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox