Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19721: json_value with DEFAULT ON ERROR returns inconsistent
 results for NULL input in materialized CTE
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 1482694023@qq.com
Reply-To: 1482694023@qq.com, pgsql-bugs@lists.postgresql.org
Date: Sat, 26 Sep 2026 06:26:06 +0000
Message-ID: <19721-6d5c523ab7ee25e4@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19721-6d5c523ab7ee25e4%40postgresql.org>
Precedence: bulk

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:       =20

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.