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

Richard Guo <guofenglinux@gmail.com>

From: Richard Guo <guofenglinux@gmail.com>

To: Tender Wang <tndrwang@gmail.com>

Cc: Tom Lane <tgl@sss.pgh.pa.us>, runyuan@berkeley.edu, pgsql-bugs@lists.postgresql.org

Date: 2025-09-16T10:18:45Z

Lists: pgsql-bugs

Commits

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

Treat JsonConstructorExpr as non-strict
- 62397bb1893b 16.11 landed
- d719e2ecb188 17.7 landed
- d29a3f4b46a0 18.0 landed
- b63a82245215 19 (unreleased) landed
Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.
- cb8e50a4a09f 18.0 cited

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