BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'
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
Hi,
The formatting above seems a bit messed up, so I am re-attaching the diff file.
--
Regards,
Man Zeng
Import Notes
Reply to msg id not found: 19625-683b498c92087bc8@postgresql.orgReference msg id not found: 19625-683b498c92087bc8@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.
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
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'::jsonbBug #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
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