BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'

Started by PG Bug reporting form3 months ago7 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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

#2zengman
zengman@halodbtech.com
In reply to: PG Bug reporting form (#1)
Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'

Hi,

The formatting above seems a bit messed up, so I am re-attaching the diff file.

--
Regards,
Man Zeng

Attachments:

t253456_1
json-bool-default-fix.patchapplication/octet-stream; charset=gb18030; name=json-bool-default-fix.patchDownload+9-12
#3zengman
zengman@halodbtech.com
In reply to: zengman (#2)
Re:BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'

Added regression tests and rebased.

--
Regards,
Man Zeng

Attachments:

t253456_2
0002-json-bool-default-fix.patchapplication/octet-stream; charset=gb18030; name=0002-json-bool-default-fix.patchDownload+33-12
#4QQ Mail
781408517@qq.com
In reply to: zengman (#3)
Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'

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.

#5Amit Langote
amitlangote09@gmail.com
In reply to: QQ Mail (#4)
Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'

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:

t253457_5
v2-0001-SQL-JSON-Fix-boolean-DEFAULT-expressions-in-ON-ER.patchapplication/octet-stream; name=v2-0001-SQL-JSON-Fix-boolean-DEFAULT-expressions-in-ON-ER.patchDownload+125-11
#6zengman
zengman@halodbtech.com
In reply to: Amit Langote (#5)
Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'

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

#7Joao Rito
joaovictor.rito@gmail.com
In reply to: zengman (#6)
Re: BUG #19625: SQL/JSON boolean DEFAULT expression silently replaced with 'false'

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