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-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