agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19721: json_value with DEFAULT ON ERROR returns inconsistent results for NULL input in materialized CTE
2+ messages / 2 participants
[nested] [flat]
* BUG #19721: json_value with DEFAULT ON ERROR returns inconsistent results for NULL input in materialized CTE
@ 2026-09-26 06:26 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-26 06:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 1482694023@qq.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19721: json_value with DEFAULT ON ERROR returns inconsistent results for NULL input in materialized CTE
@ 2026-09-26 08:55 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Andrey Rachitskiy @ 2026-09-26 08:55 UTC (permalink / raw)
To: 1482694023@qq.com; pgsql-bugs@lists.postgresql.org
сб, 26 сент. 2026 г. в 12:15, PG Bug reporting form <noreply@postgresql.org
>:
> 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.
>
>
>
>
>
Hi,
Thanks for the report.
This is the same leftover as BUG #19621 [0].
Row 1 hits ON ERROR and leaves the error flag set. Row 2 is a SQL NULL from
the LEFT JOIN, so path evaluation is skipped and the leftover flag still
applies DEFAULT 0. Row 3 clears the flags. Row 4 is NULL again and
correctly returns NULL.
MATERIALIZED only fixes the row order. Both NULL documents should return
NULL.
v2 from the #19621 thread fixes this. No separate patch is needed.
[0] -
https://www.postgresql.org/message-id/CAB8bMitawD%3DERVLwYuDXvMT_OJqf%2BqAKx%3D3GEH6V_WHT7jnq7A%40ma...
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-26 08:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-26 06:26 BUG #19721: json_value with DEFAULT ON ERROR returns inconsistent results for NULL input in materialized CTE PG Bug reporting form <noreply@postgresql.org>
2026-09-26 08:55 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox