Thread

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