BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253430psql -h localhost -U postgresBuilt from patchset v5 (message #5), September 09, 2026 at 09:02 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253430_5 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253430_5 && git checkout t253430_5Patchset v5 (message #5) is on t253430_5
The following bug has been logged on the website:
Bug reference: 19621
Logged by: Suyang Zhong
Email address: syzhong16@gmail.com
PostgreSQL version: 19beta3
Operating system: Ubuntu 22.04
Description:
Consider the following test case:
```sql
CREATE TABLE t0(x text);
INSERT INTO t0 VALUES ('{}'), (NULL);
CREATE VIEW v0 AS
SELECT x, json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
FROM t0;
SELECT x IS NULL AS is_null, jv FROM v0;
-- f | 42
-- t | 42
SELECT jv FROM v0 WHERE x IS NULL;
-- NULL
```
The second query selects exactly the row the first query shows as `t | 42`,
yet returns NULL for its `jv`.
A further reduction shows that the result depends on the input row order.
```sql
SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
('{}'), (NULL)) v(x);
-- 42
-- 42
SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
(NULL), ('{}')) v(x);
-- NULL
-- 42
```
Reproduced on 20devel, and on 17.11, 18.1, 18.4, and 19beta3; on 17rc1 only
the `ON ERROR` variants misbehave.
вс, 16 авг. 2026 г. в 17:36, PG Bug reporting form <noreply@postgresql.org>:
Consider the following test case:
```sql
CREATE TABLE t0(x text);
INSERT INTO t0 VALUES ('{}'), (NULL);
CREATE VIEW v0 AS
SELECT x, json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
FROM t0;SELECT x IS NULL AS is_null, jv FROM v0;
-- f | 42
-- t | 42SELECT jv FROM v0 WHERE x IS NULL;
-- NULL
```The second query selects exactly the row the first query shows as `t | 42`,
yet returns NULL for its `jv`.A further reduction shows that the result depends on the input row order.
```sql
SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
('{}'), (NULL)) v(x);
-- 42
-- 42
SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
(NULL), ('{}')) v(x);
-- NULL
-- 42
```Reproduced on 20devel, and on 17.11, 18.1, 18.4, and 19beta3; on 17rc1 only
the `ON ERROR` variants misbehave.Hi, Suyang!
Thanks for the report!
On NULL input, jsonpath is deliberately not run: there is nothing to
search, so the result is NULL (a NOT NULL domain still checks the NULL).
The "found empty" / "had an error" flags were cleared only in the step
that runs jsonpath. NULL skips that step, so the previous row's flags
remain. The next check is "empty? then DEFAULT" — and it fires for the
wrong row.
This dates to SQL/JSON itself (6185c973, March 2024): NULL skips path
evaluation, the reset lived inside that evaluation. Later (dd8bea88abf)
the extra steps were omitted for the default "just return NULL", so the
bug stayed hidden. A non-NULL DEFAULT (42, TRUE ON ERROR) keeps those
steps — the bug shows. On 17rc1 the reporter mostly saw ON ERROR: ON
EMPTY DEFAULT did not always go through the same path yet.
Proposal fix
-----------
Clear "empty/error" at the start of each row, before deciding not to
run the path on NULL. NULL still does not run the path. The only change
is that another row's DEFAULT no longer sticks.
CC Amit Langote (JSON).
Hi everyone,
I have another question related to JSON functions. Although it is a different issue, I wonder whether it would make sense to handle both cases in the same patch.
I tested the current patch, but it does not seem to address the problem I reported here:
```
/messages/by-id/19625-683b498c92087bc8@postgresql.org
```
--
Regards,
Man Zeng
вт, 18 авг. 2026 г. в 07:06, zengman <zengman@halodbtech.com>:
I tested the current patch, but it does not seem to address the problem I
reported here:
```
Dear Zeng,
One is per-row executor state. The other is a wrong parse-time rewrite of a
boolean DEFAULT. They share only that both involve SQL/JSON DEFAULT.
Combining them would mix an executor opcode change with a parser coercion
change, and it would make review and back-patching harder.
--
Regards,
Rachitskiy Andrey
вт, 18 авг. 2026 г. в 13:56, Andrey Rachitskiy <pl0h0yp1@gmail.com>:
вт, 18 авг. 2026 г. в 07:06, zengman <zengman@halodbtech.com>:
I tested the current patch, but it does not seem to address the problem I
reported here:
```Dear Zeng,
One is per-row executor state. The other is a wrong parse-time rewrite of
a boolean DEFAULT. They share only that both involve SQL/JSON DEFAULT.
Combining them would mix an executor opcode change with a parser coercion
change, and it would make review and back-patching harder.
Dear Amit,
Attached is v2 of the patch.
v1 was a malformed unified diff: three context lines after the
ExecEvalJsonIsPredicate hunk were missing the leading space, so
git apply rejected the file as corrupt. There is no code change
versus v1.
I also re-checked the reporter's JSON_EXISTS / JSON_VALUE /
JSON_QUERY examples from the later duplicate report [0]/messages/by-id/19654-3acd06154d027634@postgresql.org; they pass
with this patch.
[0]: /messages/by-id/19654-3acd06154d027634@postgresql.org
/messages/by-id/19654-3acd06154d027634@postgresql.org
--
Regards,
Rachitskiy Andrey