Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Treat JsonConstructorExpr as non-strict

  2. Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.

  1. BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    PG Bug reporting form <noreply@postgresql.org> — 2025-09-10T07:14:35Z

    The following bug has been logged on the website:

Bug reference:      19046
Logged by:          Runyuan He
Email address:      runyuan@berkeley.edu
PostgreSQL version: 18rc1
Operating system:   Linux (x86)
Description:        

Bug Description:
When using json_array() function with a column reference from table t inside
a subquery, and then performing a RIGHT JOIN with condition FALSE, the
result incorrectly returns [3, 2] instead of the expected NULL value.

Reproducible Example:
CREATE TABLE t(c INT);
INSERT INTO t VALUES (1);

SELECT sub.c FROM
(SELECT json_array(3, 2, t.c) AS c FROM t) AS sub
RIGHT JOIN t ON FALSE;
-- PostgreSQL 16.x: Returns NULL (CORRECT)
-- PostgreSQL 17.6, 17.rc1: Returns [3, 2] (INCORRECT)
-- PostgreSQL 18rc1: Returns [3, 2] (INCORRECT)

SELECT sub.c FROM
(SELECT json_array(3, 2, t.c) AS c FROM t) AS sub;
-- Returns [3, 2, 1] (CORRECT)

SELECT sub.c FROM
(SELECT json_array(3, 2, 1) AS c FROM t) AS sub
RIGHT JOIN t ON FALSE;
-- Returns Null (CORRECT)

  2. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Tender Wang <tndrwang@gmail.com> — 2025-09-10T11:36:52Z

    PG Bug reporting form <noreply@postgresql.org> 于2025年9月10日周三 18:22写道：

> The following bug has been logged on the website:
>
> Bug reference:      19046
> Logged by:          Runyuan He
> Email address:      runyuan@berkeley.edu
> PostgreSQL version: 18rc1
> Operating system:   Linux (x86)
> Description:
>
> Bug Description:
> When using json_array() function with a column reference from table t
> inside
> a subquery, and then performing a RIGHT JOIN with condition FALSE, the
> result incorrectly returns [3, 2] instead of the expected NULL value.
>
> Reproducible Example:
> CREATE TABLE t(c INT);
> INSERT INTO t VALUES (1);
>
> SELECT sub.c FROM
> (SELECT json_array(3, 2, t.c) AS c FROM t) AS sub
> RIGHT JOIN t ON FALSE;
> -- PostgreSQL 16.x: Returns NULL (CORRECT)
> -- PostgreSQL 17.6, 17.rc1: Returns [3, 2] (INCORRECT)
> -- PostgreSQL 18rc1: Returns [3, 2] (INCORRECT)
>
> SELECT sub.c FROM
> (SELECT json_array(3, 2, t.c) AS c FROM t) AS sub;
> -- Returns [3, 2, 1] (CORRECT)
>
> SELECT sub.c FROM
> (SELECT json_array(3, 2, 1) AS c FROM t) AS sub
> RIGHT JOIN t ON FALSE;
> -- Returns Null (CORRECT)
>
>
I can reproduce this on HEAD. The following commit introduced this
incorrect result:

commit cb8e50a4a09fe541e32cd54ea90a97f2924121a1
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Fri Aug 30 12:42:12 2024 -0400

    Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.



-- 
Thanks,
Tender Wang

  3. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Tender Wang <tndrwang@gmail.com> — 2025-09-10T12:30:59Z

    Tender Wang <tndrwang@gmail.com> 于2025年9月10日周三 19:36写道：

>
>
> PG Bug reporting form <noreply@postgresql.org> 于2025年9月10日周三 18:22写道：
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      19046
>> Logged by:          Runyuan He
>> Email address:      runyuan@berkeley.edu
>> PostgreSQL version: 18rc1
>> Operating system:   Linux (x86)
>> Description:
>>
>> Bug Description:
>> When using json_array() function with a column reference from table t
>> inside
>> a subquery, and then performing a RIGHT JOIN with condition FALSE, the
>> result incorrectly returns [3, 2] instead of the expected NULL value.
>>
>> Reproducible Example:
>> CREATE TABLE t(c INT);
>> INSERT INTO t VALUES (1);
>>
>> SELECT sub.c FROM
>> (SELECT json_array(3, 2, t.c) AS c FROM t) AS sub
>> RIGHT JOIN t ON FALSE;
>> -- PostgreSQL 16.x: Returns NULL (CORRECT)
>> -- PostgreSQL 17.6, 17.rc1: Returns [3, 2] (INCORRECT)
>> -- PostgreSQL 18rc1: Returns [3, 2] (INCORRECT)
>>
>> SELECT sub.c FROM
>> (SELECT json_array(3, 2, t.c) AS c FROM t) AS sub;
>> -- Returns [3, 2, 1] (CORRECT)
>>
>> SELECT sub.c FROM
>> (SELECT json_array(3, 2, 1) AS c FROM t) AS sub
>> RIGHT JOIN t ON FALSE;
>> -- Returns Null (CORRECT)
>>
>>
> I can reproduce this on HEAD. The following commit introduced this
> incorrect result:
>
> commit cb8e50a4a09fe541e32cd54ea90a97f2924121a1
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date:   Fri Aug 30 12:42:12 2024 -0400
>
>     Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.
>
>
The calling  contain_nonstrict_functions() returned false when
checking JsonConstructorExpr.
postgres=# SELECT json_array(3, 2, NULL);
 json_array
------------
 [3, 2]
(1 row)

The  JsonConstructorExpr seems non-strict function.

diff --git a/src/backend/optimizer/util/clauses.c
b/src/backend/optimizer/util/clauses.c
index 6f0b338d2cd..5ef364b7f7c 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1115,6 +1115,8 @@ contain_nonstrict_functions_walker(Node *node, void
*context)
                return true;
        if (IsA(node, BooleanTest))
                return true;
+       if (IsA(node, JsonConstructorExpr))
+               return true;

I added the above codes, then the query returned the correct result.
I didn't dig more the details. Any thought?
-- 
Thanks,
Tender Wang

  4. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Richard Guo <guofenglinux@gmail.com> — 2025-09-10T13:29:41Z

    On Wed, Sep 10, 2025 at 9:31 PM Tender Wang <tndrwang@gmail.com> wrote:
>> PG Bug reporting form <noreply@postgresql.org> 于2025年9月10日周三 18:22写道：
>>> SELECT sub.c FROM
>>> (SELECT json_array(3, 2, t.c) AS c FROM t) AS sub
>>> RIGHT JOIN t ON FALSE;

> diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
> index 6f0b338d2cd..5ef364b7f7c 100644
> --- a/src/backend/optimizer/util/clauses.c
> +++ b/src/backend/optimizer/util/clauses.c
> @@ -1115,6 +1115,8 @@ contain_nonstrict_functions_walker(Node *node, void *context)
>                 return true;
>         if (IsA(node, BooleanTest))
>                 return true;
> +       if (IsA(node, JsonConstructorExpr))
> +               return true;
>
> I added the above codes, then the query returned the correct result.
> I didn't dig more the details. Any thought?

Yeah, JsonConstructorExpr should not be treated as a non-strict
construct.  This fix looks correct to me.

I'm wondering if this is the only case we've overlooked.  How about
other Json-related expressions?

- Richard

  5. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Tender Wang <tndrwang@gmail.com> — 2025-09-11T01:40:27Z

    Richard Guo <guofenglinux@gmail.com> 于2025年9月10日周三 21:29写道：

> On Wed, Sep 10, 2025 at 9:31 PM Tender Wang <tndrwang@gmail.com> wrote:
> >> PG Bug reporting form <noreply@postgresql.org> 于2025年9月10日周三 18:22写道：
> >>> SELECT sub.c FROM
> >>> (SELECT json_array(3, 2, t.c) AS c FROM t) AS sub
> >>> RIGHT JOIN t ON FALSE;
>
> > diff --git a/src/backend/optimizer/util/clauses.c
> b/src/backend/optimizer/util/clauses.c
> > index 6f0b338d2cd..5ef364b7f7c 100644
> > --- a/src/backend/optimizer/util/clauses.c
> > +++ b/src/backend/optimizer/util/clauses.c
> > @@ -1115,6 +1115,8 @@ contain_nonstrict_functions_walker(Node *node,
> void *context)
> >                 return true;
> >         if (IsA(node, BooleanTest))
> >                 return true;
> > +       if (IsA(node, JsonConstructorExpr))
> > +               return true;
> >
> > I added the above codes, then the query returned the correct result.
> > I didn't dig more the details. Any thought?
>
> Yeah, JsonConstructorExpr should not be treated as a non-strict
> construct.  This fix looks correct to me.
>
> I'm wondering if this is the only case we've overlooked.  How about
> other Json-related expressions?
>

Yeah, I have the same question. I tried my fix on
json_object/json_arrayagg/json_objectagg.
These returned the same results.  But I got a different result on 16.6 for
json_object, as below:
postgres=# select version();
                                                 version

---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit

postgres=# SELECT sub.c FROM
(SELECT json_object(3:2, t.c:1) AS c FROM t) AS sub
RIGHT JOIN t ON FALSE;
ERROR:  null value not allowed for object key
postgres=# SELECT sub.c FROM
(SELECT json_object(3:2, 1:t.c) AS c FROM t) AS sub
RIGHT JOIN t ON FALSE;
           c
-----------------------
 {"3" : 2, "1" : null}
(1 row)

Shouldn't the result be NULL?

I attached my patch. In my patch, I only cover
json_array/json_arrayagg/json_object/json_objectagg.
Other JSON-related functions are not included.

-- 
Thanks,
Tender Wang

  6. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Richard Guo <guofenglinux@gmail.com> — 2025-09-11T03:10:15Z

    On Thu, Sep 11, 2025 at 10:40 AM Tender Wang <tndrwang@gmail.com> wrote:
> Richard Guo <guofenglinux@gmail.com> 于2025年9月10日周三 21:29写道：
>> I'm wondering if this is the only case we've overlooked.  How about
>> other Json-related expressions?

> Yeah, I have the same question. I tried my fix on json_object/json_arrayagg/json_objectagg.
> These returned the same results.

json_object is also represented as a JsonConstructorExpr.

json_arrayagg and json_objectagg are aggregates, so the subquery won't
be pulled up in the first place.

I tested JsonExpr, which is the representation of json_value,
json_query and json_exists.  It seems that they could not produce
non-NULL output with a NULL input.  So we are good on that front.

- Richard

  7. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-09-11T03:16:33Z

    Richard Guo <guofenglinux@gmail.com> writes:
> I tested JsonExpr, which is the representation of json_value,
> json_query and json_exists.  It seems that they could not produce
> non-NULL output with a NULL input.  So we are good on that front.

Seems like we ought to actually look at the relevant code, not
try to test our way to an understanding of it.

			regards, tom lane

  8. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Tender Wang <tndrwang@gmail.com> — 2025-09-12T00:57:27Z

    Tom Lane <tgl@sss.pgh.pa.us> 于2025年9月11日周四 11:16写道：

> Richard Guo <guofenglinux@gmail.com> writes:
> > I tested JsonExpr, which is the representation of json_value,
> > json_query and json_exists.  It seems that they could not produce
> > non-NULL output with a NULL input.  So we are good on that front.
>
> Seems like we ought to actually look at the relevant code, not
> try to test our way to an understanding of it.
>
>


I read the  ExecInitJsonExpr() code, it has:

/*
* Evaluate formatted_expr storing the result into
* jsestate->formatted_expr.
*/
ExecInitExprRec((Expr *) jsexpr->formatted_expr, state,
&jsestate->formatted_expr.value,
&jsestate->formatted_expr.isnull);

/* JUMP to return NULL if formatted_expr evaluates to NULL */
jumps_return_null = lappend_int(jumps_return_null, state->steps_len);
scratch->opcode = EEOP_JUMP_IF_NULL;
scratch->resnull = &jsestate->formatted_expr.isnull;
scratch->d.jump.jumpdone = -1; /* set below */
ExprEvalPushStep(state, scratch);

The above codes say that it will directly return null if formatted_expr
evaluates to NULL.

-- 
Thanks,
Tender Wang

  9. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Richard Guo <guofenglinux@gmail.com> — 2025-09-16T01:05:37Z

    On Fri, Sep 12, 2025 at 9:57 AM Tender Wang <tndrwang@gmail.com> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> 于2025年9月11日周四 11:16写道：
>> Seems like we ought to actually look at the relevant code, not
>> try to test our way to an understanding of it.

> /* JUMP to return NULL if formatted_expr evaluates to NULL */
> jumps_return_null = lappend_int(jumps_return_null, state->steps_len);
> scratch->opcode = EEOP_JUMP_IF_NULL;
> scratch->resnull = &jsestate->formatted_expr.isnull;
> scratch->d.jump.jumpdone = -1; /* set below */
> ExprEvalPushStep(state, scratch);

Yeah, this suggests that JsonExpr behaves as a strict construct.

There may be other functions that should be treated as non-strict but
currently aren't.  For now, I suggest we address JsonConstructorExpr
first.  If we encounter more such cases later, they should be
straightforward to fix as well.

- Richard

  10. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Richard Guo <guofenglinux@gmail.com> — 2025-09-16T10:18:45Z

    On Tue, Sep 16, 2025 at 10:05 AM Richard Guo <guofenglinux@gmail.com> wrote:
> There may be other functions that should be treated as non-strict but
> currently aren't.  For now, I suggest we address JsonConstructorExpr
> first.  If we encounter more such cases later, they should be
> straightforward to fix as well.

I don't like the test cases in the proposed patch.  First, the test
output is excessively large, resulting in a patch with over 30,000
insertions.  I don't think it's a good idea to output the entire tenk1
table.  Second, I don't think it's useful to test json_arrayagg or
json_objectagg.  As I mentioned earlier, these are aggregates, so the
subquery wouldn't be pulled up in the first place.

I've rewritten the test cases, added a commit message, and pushed the
patch.  It has also been back-patched to v16.

- Richard

  11. Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN

    Tender Wang <tndrwang@gmail.com> — 2025-09-16T10:31:49Z

    Richard Guo <guofenglinux@gmail.com> 于2025年9月16日周二 18:18写道：

> On Tue, Sep 16, 2025 at 10:05 AM Richard Guo <guofenglinux@gmail.com>
> wrote:
> > There may be other functions that should be treated as non-strict but
> > currently aren't.  For now, I suggest we address JsonConstructorExpr
> > first.  If we encounter more such cases later, they should be
> > straightforward to fix as well.
>
> I don't like the test cases in the proposed patch.  First, the test
> output is excessively large, resulting in a patch with over 30,000
> insertions.  I don't think it's a good idea to output the entire tenk1
> table.  Second, I don't think it's useful to test json_arrayagg or
> json_objectagg.  As I mentioned earlier, these are aggregates, so the
> subquery wouldn't be pulled up in the first place.
>

Thanks a lot!
Your test cases look better.


>
> I've rewritten the test cases, added a commit message, and pushed the
> patch.  It has also been back-patched to v16.
>
> - Richard
>

Thanks for pushing.
-- 
Thanks,
Tender Wang