BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

Поиск
Список
Период
Сортировка
Искать

BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.




Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:
Вложения:

вт, 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:
```
https://www.postgresql.org/message-id/19625-683b498c92087bc8%40postgresql.org
```
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]; they pass
with this patch. 



--
Regards,
Rachitskiy Andrey

Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Hi Andrey,

I tested v2 on master, REL_18_STABLE and REL_17_STABLE, together with
Srinath's fix for #19695 [1], which touches the same function.  Both
apply cleanly on all three branches, build without warnings, and the
main regression suite passes.  The reported cases now give the right
answer on the three branches:

- #19621: json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) over
  '{}' then NULL returns 42, NULL (it was 42, 42)
- #19654: json_exists(j, 'strict $.a' FALSE ON ERROR) over '{}' then
  NULL returns false, NULL (it was false, false)

I could not exercise the llvmjit_expr.c part: this machine has no LLVM
build.

One thing for the back-patch.  EEOP_JSONEXPR_RESET is inserted before
EEOP_JSONEXPR_PATH, so every opcode from there to EEOP_LAST changes its
value: 24 of them on REL_17_STABLE and 25 on REL_18_STABLE.  On master
that is fine, but the back branches are under the buildfarm's ABI
compliance check (.abi-compliance-history), and anything built against
a minor release that uses those values would be off by one after the
update.  Putting the new opcode just before EEOP_LAST in the
back-branch versions, with the dispatch table and llvmjit_expr.c
following the same order, would leave the existing values alone.

If you post back-branch versions, I will rerun the same checks on
them.

[1]
https://postgr.es/m/CAFC+b6p8Oo3_pLXPa=DVXx1XDQJ09b2W3U3WKPtkQYyh0z0z-A@mail.gmail.com

Regards,
Manu


Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Дата:
Hi,

Patch LGTM , I have tested with LLVM build as well , it works as intended.

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:


чт, 24 сент. 2026 г., 22:07 Srinath Reddy Sadipiralla <srinath2133@gmail.com>:
Hi,

Patch LGTM , I have tested with LLVM build as well , it works as intended.

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

Hi, Srinath!
Thnx for the review!

Manu, thnx for the testing.

---
Regards,
Rachitskiy Andrey

Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
jian he <jian.universality@gmail.com>
Дата:
Вложения:
On Fri, Sep 4, 2026 at 6:38 PM Andrey Rachitskiy  wrote:
>
> Attached is v2 of the patch.
>
> --
> Regards,
> Rachitskiy Andrey


+ EEO_CASE(EEOP_JSONEXPR_RESET)
+ {
+ ExecEvalJsonExprReset(state, op);
+
+ EEO_NEXT();
+ }
+

--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -265,6 +265,7 @@ typedef enum ExprEvalOp
  EEOP_XMLEXPR,
  EEOP_JSON_CONSTRUCTOR,
  EEOP_IS_JSON,
+ EEOP_JSONEXPR_RESET,
  EEOP_JSONEXPR_PATH,
  EEOP_JSONEXPR_COERCION,
  EEOP_JSONEXPR_COERCION_FINISH,

This seems unnecessary.
In EEOP_JSONEXPR_PATH, we can
if document or jsonpath is NULL, we can just go to jump_end (return
NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
no need to worry about ON ERROR, ON EMPTY.

What do you think of the attachment?



--
jian
https://www.enterprisedb.com/

Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Вложения:
Hi jian,

> This seems unnecessary.
> In EEOP_JSONEXPR_PATH, we can
> if document or jsonpath is NULL, we can just go to jump_end (return
> NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
> no need to worry about ON ERROR, ON EMPTY.

That also takes care of the back-branch concern I raised on v2: with
no new opcode, v3 does not change any header, so the ExprEvalOp values
stay as they are.

v3 applies cleanly to master, REL_18_STABLE and REL_17_STABLE, builds
without warnings, and the main regression suite passes on all three.

Since the bug is state leaking from one row to the next, I also
checked it with a differential test (attached): every row of a
two-row query must give the same result as that row evaluated alone.
It covers json_value (RETURNING int, a NOT NULL domain and a CHECK
domain), json_query and json_exists, with every ON EMPTY / ON ERROR
combination, over every ordered pair of rows built from five
documents ('{}', '{"a":1}', '{"a":"x"}', '{"a":[1,2]}', NULL) and
three paths ('$.a', 'strict $.a', NULL).  That is 9675 cases:

- unpatched master (09a579abaca): 567 mismatches (497 json_value,
  56 json_query, 14 json_exists)
- v3 on master, REL_18_STABLE and REL_17_STABLE: 0

The single-row answers do not change: all 645 of them are the same
with and without v3, including NULL input into the NOT NULL domain.

With Srinath's fix for #19695 on top, its case is right too
(1, NULL, 2).  As before, I could not exercise JIT here.

Regards,
Manu

Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:
Вложения:


сб, 26 сент. 2026 г. в 21:29, jian he <jian.universality@gmail.com>:
On Fri, Sep 4, 2026 at 6:38 PM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
>
> Attached is v2 of the patch.
>
> --
> Regards,
> Rachitskiy Andrey


+ EEO_CASE(EEOP_JSONEXPR_RESET)
+ {
+ ExecEvalJsonExprReset(state, op);
+
+ EEO_NEXT();
+ }
+

--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -265,6 +265,7 @@ typedef enum ExprEvalOp
  EEOP_XMLEXPR,
  EEOP_JSON_CONSTRUCTOR,
  EEOP_IS_JSON,
+ EEOP_JSONEXPR_RESET,
  EEOP_JSONEXPR_PATH,
  EEOP_JSONEXPR_COERCION,
  EEOP_JSONEXPR_COERCION_FINISH,

This seems unnecessary.
In EEOP_JSONEXPR_PATH, we can
if document or jsonpath is NULL, we can just go to jump_end (return
NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
no need to worry about ON ERROR, ON EMPTY.

What do you think of the attachment?



--
jian
https://www.enterprisedb.com/

Hi, Jian!

Thanks for posting this alternative.

I considered the same shape earlier: keep the empty/error reset in EEOP_JSONEXPR_PATH and stop skipping that step on SQL NULL. Your version is simpler than adding EEOP_JSONEXPR_RESET. No new opcode, and no JIT or back-branch ABI churn.

The commit message still described a RESET opcode that the diff does not add. I adjusted the subject and body to match what the patch actually does (retarget JUMP_IF_NULL at PATH, drop the CONST NULL pad, handle NULL inside ExecEvalJsonExprPath).

Either approach fixes the reported cases. I am fine with whichever version a committer prefers.


Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Amit Langote <amitlangote09@gmail.com>
Дата:
Вложения:
Hi,

On Mon, Sep 28, 2026 at 1:03 PM Andrey Rachitskiy  wrote:
> сб, 26 сент. 2026 г. в 21:29, jian he :
>> On Fri, Sep 4, 2026 at 6:38 PM Andrey Rachitskiy  wrote:
>> >
>> > Attached is v2 of the patch.
>> >
>> > --
>> > Regards,
>> > Rachitskiy Andrey
>>
>>
>> + EEO_CASE(EEOP_JSONEXPR_RESET)
>> + {
>> + ExecEvalJsonExprReset(state, op);
>> +
>> + EEO_NEXT();
>> + }
>> +
>>
>> --- a/src/include/executor/execExpr.h
>> +++ b/src/include/executor/execExpr.h
>> @@ -265,6 +265,7 @@ typedef enum ExprEvalOp
>>   EEOP_XMLEXPR,
>>   EEOP_JSON_CONSTRUCTOR,
>>   EEOP_IS_JSON,
>> + EEOP_JSONEXPR_RESET,
>>   EEOP_JSONEXPR_PATH,
>>   EEOP_JSONEXPR_COERCION,
>>   EEOP_JSONEXPR_COERCION_FINISH,
>>
>> This seems unnecessary.
>> In EEOP_JSONEXPR_PATH, we can
>> if document or jsonpath is NULL, we can just go to jump_end (return
>> NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
>> no need to worry about ON ERROR, ON EMPTY.
>>
>> What do you think of the attachment?
>>
>>
>>
>> --
>> jian
>> https://www.enterprisedb.com/
>
>
> Hi, Jian!
>
> Thanks for posting this alternative.
>
> I considered the same shape earlier: keep the empty/error reset in EEOP_JSONEXPR_PATH and stop skipping that step on SQL NULL. Your version is simpler than adding EEOP_JSONEXPR_RESET. No new opcode, and no JIT or back-branch ABI churn.
>
> The commit message still described a RESET opcode that the diff does not add. I adjusted the subject and body to match what the patch actually does (retarget JUMP_IF_NULL at PATH, drop the CONST NULL pad, handle NULL inside ExecEvalJsonExprPath).
>
> Either approach fixes the reported cases. I am fine with whichever version a committer prefers.

Thanks, Jian and Andrey. I agree that v3’s approach of having
ExecEvalJsonExprPath() itself handle a NULL context item or path is
better than adding a new code to do that job, which as you have
pointed out is not painless to backpatch.

Here is v4 with some comment and commit message updates, plus one more
regression case for JSON_EXISTS’s default FALSE ON ERROR. I plan to
commit and back-patch it to 17 barring objections.

-- 
Thanks, Amit Langote

Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:

ср, 4 нояб. 2026 г., 15:06 Amit Langote <amitlangote09@gmail.com>:
 
Here is v4 with some comment and commit message updates, plus one more
regression case for JSON_EXISTS’s default FALSE ON ERROR. I plan to
commit and back-patch it to 17 barring objections.

Hi Amit,

LGTM

Thanks for the review

Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY

От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:

Amit, thnx for pushing.

--
Regards,
Rachitskiy Andrey
FAQ