agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 1482694023@qq.com
Subject: BUG #19721: json_value with DEFAULT ON ERROR returns inconsistent results for NULL input in materialized CTE
Date: Sat, 26 Sep 2026 06:26:06 +0000
Message-ID: <19721-6d5c523ab7ee25e4@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19721
Logged by: N J
Email address: 1482694023@qq.com
PostgreSQL version: 18.6
Operating system: windows 11
Description:
Environment:
PostgreSQL: 18.6
OS: Windows 11 64-bit
Reproduction SQL:
CREATE TEMP TABLE json_requests (
request_id integer PRIMARY KEY,
expected integer
);
CREATE TEMP TABLE json_payloads (
request_id integer PRIMARY KEY REFERENCES json_requests(request_id),
document jsonb NOT NULL
);
INSERT INTO json_requests VALUES
(1, 0), (2, NULL), (3, 7), (4, NULL);
INSERT INTO json_payloads VALUES
(1, '{}'::jsonb),
(3, '{"a":7}'::jsonb);
WITH ordered_inputs AS MATERIALIZED (
SELECT r.request_id, r.expected, p.document
FROM json_requests AS r
LEFT JOIN json_payloads AS p USING (request_id)
ORDER BY r.request_id
),
evaluated AS MATERIALIZED (
SELECT request_id, expected,
json_value(
document,
'strict $.a'
RETURNING integer
DEFAULT 0 ON ERROR
) AS actual
FROM ordered_inputs
)
SELECT request_id, expected, actual,
actual IS DISTINCT FROM expected AS differs
FROM evaluated
ORDER BY request_id;
Observed result:
request_id | expected | actual | differs
------------+----------+--------+---------
1 | 0 | 0 | f
2 | null | 0 | t
3 | 7 | 7 | f
4 | null | null| f
Expected result:
Rows 2 and 4 both have NULL document from the LEFT JOIN.
The identical json_value expression should return the same result for both
NULL inputs.
Either both should return 0 (DEFAULT value) or both should return NULL.
The current behavior is inconsistent.
Explanation:
The inconsistency occurs when using MATERIALIZED CTEs with json_value and
DEFAULT ON ERROR clause.
Same NULL input produces different output values in different rows, which is
an execution consistency bug.
view thread (2+ messages) latest in thread
Message-ID: <19721-6d5c523ab7ee25e4@postgresql.org>
Permalink: ../19721-6d5c523ab7ee25e4@postgresql.org/
Also on: postgresql.org/message-id/19721-6d5c523ab7ee25e4@postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 1482694023@qq.com
Subject: Re: BUG #19721: json_value with DEFAULT ON ERROR returns inconsistent results for NULL input in materialized CTE
In-Reply-To: <19721-6d5c523ab7ee25e4@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox